| Property | Value |
| Model name | New model |
| Technical name | |
| Description | |
| Author | |
| Version | |
| Synchronization Id | |
| DB vendor | PostgreSQL |
| DB version | v13.x |
| Database name | dvdrental |
| Tablespace | pg_default |
| Encoding | UTF8 |
| Template | |
| Locale | |
| Collation | en_US.UTF-8 |
| Character type | en_US.UTF-8 |
| Lineage capture | true |
| Polyglot models | |
| Comments |
| Property | Value |
| Model sources | |
| [1] Source name | RE'd from instance |
| Source ID | 17369186-dae8-4af8-9db5-b86e765aaf0e |
| Description | |
| Source format | Instance |
| Connection name | HackoladeLocalPostgres |
| Timestamp | 2023-06-08 18:56:12 CEST |
| Property | Value |
| Business Name | mpaa_rating |
| Technical name | |
| Id | |
| Type | enum |
| Comments | |
| Not null | |
| Pattern | |
| Default | |
| Enum | G,PG,PG-13,R,NC-17 |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | year |
| Technical name | |
| Id | |
| Type | domain |
| Underlying type | integer |
| Collation | |
| Not null | false |
| Default | |
| Check constraints | |
| [1] Constraint name | year_check |
| Check expression | ((VALUE >= 1901) AND (VALUE <= 2155)) |
| Comments | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Schema name | public |
| Technical name | |
| Activated | true |
| Comments | |
| If not exist | true |
| Remarks |
| Property | Value |
| Table | actor |
| Technical name | |
| Activated | true |
| Id | |
| Schema | public |
| Additional properties | true |
| $ref | |
| $ref | |
| Comments | |
| Temporary | false |
| Unlogged | false |
| If not exists | true |
| Inherits parent tables | |
| [1] Table name | |
| Partition of | |
| Partition bounds | |
| Partitioning | |
| [1] Partition method | |
| Partition by | |
| Partition key | |
| Expression | |
| Using method | |
| Storage parameters | |
| [1] Fill factor | |
| Parallel workers | |
| Enable autovacuum | |
| Autovacuum params | |
| Enable TOAST autovacuum | |
| TOAST parameters | |
| User catalog table | |
| Tablespace | pg_default |
| As Select statement | |
| Remarks | |
| Column | Type | Req | Key | Description | Comments |
| actor_id | integer | true | pk, dk | ||
| first_name | varchar(45) | true | |||
| last_name | varchar(45) | true | |||
| last_update | timestamp | true |
| Property | Value |
| Business Name | actor_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | integer |
| Array type | |
| Not null | true |
| Default | nextval('actor_actor_id_seq'::regclass) |
| Comments | |
| Primary key | true |
| Primary key options | |
| [1] Constraint name | actor_pkey |
| Include non-key columns | |
| With storage parameters | |
| Tablespace | |
| Comment | |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | first_name |
| Technical name | |
| Activated | true |
| Id | |
| Type | char |
| Subtype | varchar |
| Length | 45 |
| Array type | |
| Collation rule | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Min length | |
| Max length | |
| Pattern | |
| Format | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | last_name |
| Technical name | |
| Activated | true |
| Id | |
| Type | char |
| Subtype | varchar |
| Length | 45 |
| Array type | |
| Collation rule | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Min length | |
| Max length | |
| Pattern | |
| Format | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | last_update |
| Technical name | |
| Activated | true |
| Id | |
| Type | datetime |
| Subtype | timestamp |
| Precision | |
| Timezone | WITHOUT TIME ZONE |
| Comments | |
| Array type | |
| Not null | true |
| Default | now() |
| Primary key | false |
| Unique | false |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Primary key | |
| [1] Constraint name | |
| Key | |
| Include non-key columns | |
| With storage parameters | |
| Index tablespace | |
| Comment | |
| Unique key | |
| [1] Constraint name | |
| Key | |
| Include non-key columns | |
| With storage parameters | |
| Index tablespace | |
| Comment | |
| Nulls Distinct | |
| Property | |
| Name | actor_pkey |
| Activated | true |
| Method | btree |
| Unique | true |
| Nulls Distinct | |
| Concurrent build | |
| If not exist | true |
| Only | true |
| Columns | |
| actor_id | |
| Include non-key columns | |
| With storage parameters | |
| [1] Fill factor | |
| Deduplicate items | |
| Tablespace | |
| Where constraint | |
| Comment | |
| Property | |
| Name | idx_actor_last_name |
| Activated | true |
| Method | btree |
| Unique | |
| Concurrent build | |
| If not exist | true |
| Only | true |
| Columns | |
| last_name | |
| Include non-key columns | |
| With storage parameters | |
| [1] Fill factor | |
| Deduplicate items | |
| Tablespace | |
| Where constraint | |
| Comment | |
| Property | last_updated |
| Name | last_updated |
| Description | |
| Or replace | |
| Constraint | |
| Trigger type | BEFORE |
| Trigger events | |
| [1] Event | UPDATE |
| Update columns | |
| Referencing | |
| Trigger for each row/statement | FOR EACH ROW |
| Trigger WHEN condition | |
| Function | last_updated() |
| Remarks | |
{
"$schema": "http://json-schema.org/draft-04/schema#",
"type": "object",
"title": "actor",
"properties": {
"actor_id": {
"type": "number",
"default": "nextval('actor_actor_id_seq'::regclass)"
},
"first_name": {
"type": "string"
},
"last_name": {
"type": "string"
},
"last_update": {
"type": "string",
"default": "now()"
}
},
"additionalProperties": true,
"required": [
"actor_id",
"first_name",
"last_name",
"last_update"
]
}{
"actor_id": "nextval('actor_actor_id_seq'::regclass)",
"first_name": "Lorem",
"last_name": "Lorem",
"last_update": "now()"
}CREATE DATABASE dvdrental
ENCODING UTF8
LC_COLLATE 'en_US.UTF-8'
LC_CTYPE 'en_US.UTF-8'
TABLESPACE 'pg_default';
CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;
/*
CREATE TYPE public.mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');
*/
/*
CREATE DOMAIN public.year AS integer
CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));
*/
CREATE TABLE IF NOT EXISTS public.actor (
actor_id integer DEFAULT nextval('actor_actor_id_seq'::regclass) NOT NULL,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
CONSTRAINT actor_pkey PRIMARY KEY (actor_id)
) TABLESPACE pg_default;
CREATE TRIGGER last_updated BEFORE UPDATE
ON public.actor
FOR EACH ROW
EXECUTE FUNCTION last_updated();
CREATE UNIQUE INDEX IF NOT EXISTS actor_pkey
ON ONLY public.actor USING BTREE (actor_id pg_catalog.int4_ops ASC NULLS LAST) ;
CREATE INDEX IF NOT EXISTS idx_actor_last_name
ON ONLY public.actor USING BTREE (last_name COLLATE pg_catalog."default" pg_catalog.text_ops ASC NULLS LAST) ;| Property | Value |
| Table | address |
| Technical name | |
| Activated | true |
| Id | |
| Schema | public |
| Additional properties | true |
| $ref | |
| $ref | |
| Comments | |
| Temporary | false |
| Unlogged | false |
| If not exists | true |
| Inherits parent tables | |
| [1] Table name | |
| Partition of | |
| Partition bounds | |
| Partitioning | |
| [1] Partition method | |
| Partition by | |
| Partition key | |
| Expression | |
| Using method | |
| Storage parameters | |
| [1] Fill factor | |
| Parallel workers | |
| Enable autovacuum | |
| Autovacuum params | |
| Enable TOAST autovacuum | |
| TOAST parameters | |
| User catalog table | |
| Tablespace | pg_default |
| As Select statement | |
| Remarks | |
| Column | Type | Req | Key | Description | Comments |
| address_id | integer | true | pk, dk | ||
| address | varchar(50) | true | |||
| address2 | varchar(50) | false | |||
| district | varchar(20) | true | |||
| city_id | smallint | true | fk | ||
| postal_code | varchar(10) | false | |||
| phone | varchar(20) | true | |||
| last_update | timestamp | true |
| Property | Value |
| Business Name | address_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | integer |
| Array type | |
| Not null | true |
| Default | nextval('address_address_id_seq'::regclass) |
| Comments | |
| Primary key | true |
| Primary key options | |
| [1] Constraint name | address_pkey |
| Include non-key columns | |
| With storage parameters | |
| Tablespace | |
| Comment | |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | address |
| Technical name | |
| Activated | true |
| Id | |
| Type | char |
| Subtype | varchar |
| Length | 50 |
| Array type | |
| Collation rule | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Min length | |
| Max length | |
| Pattern | |
| Format | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | address2 |
| Technical name | |
| Activated | true |
| Id | |
| Type | char |
| Subtype | varchar |
| Length | 50 |
| Array type | |
| Collation rule | |
| Not null | false |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Min length | |
| Max length | |
| Pattern | |
| Format | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | district |
| Technical name | |
| Activated | true |
| Id | |
| Type | char |
| Subtype | varchar |
| Length | 20 |
| Array type | |
| Collation rule | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Min length | |
| Max length | |
| Pattern | |
| Format | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | city_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | smallint |
| Array type | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | city |
| Foreign field | city_id |
| Relationship type | Foreign Key |
| Relationship name | fk_address_city |
| Cardinality | 0..n |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | postal_code |
| Technical name | |
| Activated | true |
| Id | |
| Type | char |
| Subtype | varchar |
| Length | 10 |
| Array type | |
| Collation rule | |
| Not null | false |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Min length | |
| Max length | |
| Pattern | |
| Format | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | phone |
| Technical name | |
| Activated | true |
| Id | |
| Type | char |
| Subtype | varchar |
| Length | 20 |
| Array type | |
| Collation rule | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Min length | |
| Max length | |
| Pattern | |
| Format | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | last_update |
| Technical name | |
| Activated | true |
| Id | |
| Type | datetime |
| Subtype | timestamp |
| Precision | |
| Timezone | WITHOUT TIME ZONE |
| Comments | |
| Array type | |
| Not null | true |
| Default | now() |
| Primary key | false |
| Unique | false |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Primary key | |
| [1] Constraint name | |
| Key | |
| Include non-key columns | |
| With storage parameters | |
| Index tablespace | |
| Comment | |
| Unique key | |
| [1] Constraint name | |
| Key | |
| Include non-key columns | |
| With storage parameters | |
| Index tablespace | |
| Comment | |
| Nulls Distinct | |
| Property | |
| Name | address_pkey |
| Activated | true |
| Method | btree |
| Unique | true |
| Nulls Distinct | |
| Concurrent build | |
| If not exist | true |
| Only | true |
| Columns | |
| address_id | |
| Include non-key columns | |
| With storage parameters | |
| [1] Fill factor | |
| Deduplicate items | |
| Tablespace | |
| Where constraint | |
| Comment | |
| Property | |
| Name | idx_fk_city_id |
| Activated | true |
| Method | btree |
| Unique | |
| Concurrent build | |
| If not exist | true |
| Only | true |
| Columns | |
| city_id | |
| Include non-key columns | |
| With storage parameters | |
| [1] Fill factor | |
| Deduplicate items | |
| Tablespace | |
| Where constraint | |
| Comment | |
| Property | last_updated |
| Name | last_updated |
| Description | |
| Or replace | |
| Constraint | |
| Trigger type | BEFORE |
| Trigger events | |
| [1] Event | UPDATE |
| Update columns | |
| Referencing | |
| Trigger for each row/statement | FOR EACH ROW |
| Trigger WHEN condition | |
| Function | last_updated() |
| Remarks | |
{
"$schema": "http://json-schema.org/draft-04/schema#",
"type": "object",
"title": "address",
"properties": {
"address_id": {
"type": "number",
"default": "nextval('address_address_id_seq'::regclass)"
},
"address": {
"type": "string"
},
"address2": {
"type": "string"
},
"district": {
"type": "string"
},
"city_id": {
"type": "number"
},
"postal_code": {
"type": "string"
},
"phone": {
"type": "string"
},
"last_update": {
"type": "string",
"default": "now()"
}
},
"additionalProperties": true,
"required": [
"address_id",
"address",
"district",
"city_id",
"phone",
"last_update"
]
}{
"address_id": "nextval('address_address_id_seq'::regclass)",
"address": "Lorem",
"address2": "Lorem",
"district": "Lorem",
"city_id": 7,
"postal_code": "Lorem",
"phone": "Lorem",
"last_update": "now()"
}CREATE DATABASE dvdrental
ENCODING UTF8
LC_COLLATE 'en_US.UTF-8'
LC_CTYPE 'en_US.UTF-8'
TABLESPACE 'pg_default';
CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;
/*
CREATE TYPE public.mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');
*/
/*
CREATE DOMAIN public.year AS integer
CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));
*/
CREATE TABLE IF NOT EXISTS public.address (
address_id integer DEFAULT nextval('address_address_id_seq'::regclass) NOT NULL,
address varchar(50) NOT NULL,
address2 varchar(50),
district varchar(20) NOT NULL,
city_id smallint NOT NULL,
postal_code varchar(10),
phone varchar(20) NOT NULL,
last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
CONSTRAINT address_pkey PRIMARY KEY (address_id),
CONSTRAINT fk_address_city FOREIGN KEY (city_id) REFERENCES public.city (city_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
) TABLESPACE pg_default;
CREATE TRIGGER last_updated BEFORE UPDATE
ON public.address
FOR EACH ROW
EXECUTE FUNCTION last_updated();
CREATE UNIQUE INDEX IF NOT EXISTS address_pkey
ON ONLY public.address USING BTREE (address_id pg_catalog.int4_ops ASC NULLS LAST) ;
CREATE INDEX IF NOT EXISTS idx_fk_city_id
ON ONLY public.address USING BTREE (city_id pg_catalog.int2_ops ASC NULLS LAST) ;| Property | Value |
| Table | category |
| Technical name | |
| Activated | true |
| Id | |
| Schema | public |
| Additional properties | true |
| $ref | |
| $ref | |
| Comments | |
| Temporary | false |
| Unlogged | false |
| If not exists | true |
| Inherits parent tables | |
| [1] Table name | |
| Partition of | |
| Partition bounds | |
| Partitioning | |
| [1] Partition method | |
| Partition by | |
| Partition key | |
| Expression | |
| Using method | |
| Storage parameters | |
| [1] Fill factor | |
| Parallel workers | |
| Enable autovacuum | |
| Autovacuum params | |
| Enable TOAST autovacuum | |
| TOAST parameters | |
| User catalog table | |
| Tablespace | pg_default |
| As Select statement | |
| Remarks | |
| Column | Type | Req | Key | Description | Comments |
| category_id | integer | true | pk, dk | ||
| name | varchar(25) | true | |||
| last_update | timestamp | true |
| Property | Value |
| Business Name | category_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | integer |
| Array type | |
| Not null | true |
| Default | nextval('category_category_id_seq'::regclass) |
| Comments | |
| Primary key | true |
| Primary key options | |
| [1] Constraint name | category_pkey |
| Include non-key columns | |
| With storage parameters | |
| Tablespace | |
| Comment | |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | name |
| Technical name | |
| Activated | true |
| Id | |
| Type | char |
| Subtype | varchar |
| Length | 25 |
| Array type | |
| Collation rule | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Min length | |
| Max length | |
| Pattern | |
| Format | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | last_update |
| Technical name | |
| Activated | true |
| Id | |
| Type | datetime |
| Subtype | timestamp |
| Precision | |
| Timezone | WITHOUT TIME ZONE |
| Comments | |
| Array type | |
| Not null | true |
| Default | now() |
| Primary key | false |
| Unique | false |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Primary key | |
| [1] Constraint name | |
| Key | |
| Include non-key columns | |
| With storage parameters | |
| Index tablespace | |
| Comment | |
| Unique key | |
| [1] Constraint name | |
| Key | |
| Include non-key columns | |
| With storage parameters | |
| Index tablespace | |
| Comment | |
| Nulls Distinct | |
| Property | |
| Name | category_pkey |
| Activated | true |
| Method | btree |
| Unique | true |
| Nulls Distinct | |
| Concurrent build | |
| If not exist | true |
| Only | true |
| Columns | |
| category_id | |
| Include non-key columns | |
| With storage parameters | |
| [1] Fill factor | |
| Deduplicate items | |
| Tablespace | |
| Where constraint | |
| Comment | |
| Property | last_updated |
| Name | last_updated |
| Description | |
| Or replace | |
| Constraint | |
| Trigger type | BEFORE |
| Trigger events | |
| [1] Event | UPDATE |
| Update columns | |
| Referencing | |
| Trigger for each row/statement | FOR EACH ROW |
| Trigger WHEN condition | |
| Function | last_updated() |
| Remarks | |
{
"$schema": "http://json-schema.org/draft-04/schema#",
"type": "object",
"title": "category",
"properties": {
"category_id": {
"type": "number",
"default": "nextval('category_category_id_seq'::regclass)"
},
"name": {
"type": "string"
},
"last_update": {
"type": "string",
"default": "now()"
}
},
"additionalProperties": true,
"required": [
"category_id",
"name",
"last_update"
]
}{
"category_id": "nextval('category_category_id_seq'::regclass)",
"name": "Lorem",
"last_update": "now()"
}CREATE DATABASE dvdrental
ENCODING UTF8
LC_COLLATE 'en_US.UTF-8'
LC_CTYPE 'en_US.UTF-8'
TABLESPACE 'pg_default';
CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;
/*
CREATE TYPE public.mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');
*/
/*
CREATE DOMAIN public.year AS integer
CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));
*/
CREATE TABLE IF NOT EXISTS public.category (
category_id integer DEFAULT nextval('category_category_id_seq'::regclass) NOT NULL,
name varchar(25) NOT NULL,
last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
CONSTRAINT category_pkey PRIMARY KEY (category_id)
) TABLESPACE pg_default;
CREATE TRIGGER last_updated BEFORE UPDATE
ON public.category
FOR EACH ROW
EXECUTE FUNCTION last_updated();
CREATE UNIQUE INDEX IF NOT EXISTS category_pkey
ON ONLY public.category USING BTREE (category_id pg_catalog.int4_ops ASC NULLS LAST) ;| Property | Value |
| Table | city |
| Technical name | |
| Activated | true |
| Id | |
| Schema | public |
| Additional properties | true |
| $ref | |
| $ref | |
| Comments | |
| Temporary | false |
| Unlogged | false |
| If not exists | true |
| Inherits parent tables | |
| [1] Table name | |
| Partition of | |
| Partition bounds | |
| Partitioning | |
| [1] Partition method | |
| Partition by | |
| Partition key | |
| Expression | |
| Using method | |
| Storage parameters | |
| [1] Fill factor | |
| Parallel workers | |
| Enable autovacuum | |
| Autovacuum params | |
| Enable TOAST autovacuum | |
| TOAST parameters | |
| User catalog table | |
| Tablespace | pg_default |
| As Select statement | |
| Remarks | |
| Column | Type | Req | Key | Description | Comments |
| city_id | integer | true | pk, dk | ||
| city | varchar(50) | true | |||
| country_id | smallint | true | fk | ||
| last_update | timestamp | true |
| Property | Value |
| Business Name | city_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | integer |
| Array type | |
| Not null | true |
| Default | nextval('city_city_id_seq'::regclass) |
| Comments | |
| Primary key | true |
| Primary key options | |
| [1] Constraint name | city_pkey |
| Include non-key columns | |
| With storage parameters | |
| Tablespace | |
| Comment | |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | city |
| Technical name | |
| Activated | true |
| Id | |
| Type | char |
| Subtype | varchar |
| Length | 50 |
| Array type | |
| Collation rule | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Min length | |
| Max length | |
| Pattern | |
| Format | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | country_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | smallint |
| Array type | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | country |
| Foreign field | country_id |
| Relationship type | Foreign Key |
| Relationship name | fk_city |
| Cardinality | 0..n |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | last_update |
| Technical name | |
| Activated | true |
| Id | |
| Type | datetime |
| Subtype | timestamp |
| Precision | |
| Timezone | WITHOUT TIME ZONE |
| Comments | |
| Array type | |
| Not null | true |
| Default | now() |
| Primary key | false |
| Unique | false |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Primary key | |
| [1] Constraint name | |
| Key | |
| Include non-key columns | |
| With storage parameters | |
| Index tablespace | |
| Comment | |
| Unique key | |
| [1] Constraint name | |
| Key | |
| Include non-key columns | |
| With storage parameters | |
| Index tablespace | |
| Comment | |
| Nulls Distinct | |
| Property | |
| Name | city_pkey |
| Activated | true |
| Method | btree |
| Unique | true |
| Nulls Distinct | |
| Concurrent build | |
| If not exist | true |
| Only | true |
| Columns | |
| city_id | |
| Include non-key columns | |
| With storage parameters | |
| [1] Fill factor | |
| Deduplicate items | |
| Tablespace | |
| Where constraint | |
| Comment | |
| Property | |
| Name | idx_fk_country_id |
| Activated | true |
| Method | btree |
| Unique | |
| Concurrent build | |
| If not exist | true |
| Only | true |
| Columns | |
| country_id | |
| Include non-key columns | |
| With storage parameters | |
| [1] Fill factor | |
| Deduplicate items | |
| Tablespace | |
| Where constraint | |
| Comment | |
| Property | last_updated |
| Name | last_updated |
| Description | |
| Or replace | |
| Constraint | |
| Trigger type | BEFORE |
| Trigger events | |
| [1] Event | UPDATE |
| Update columns | |
| Referencing | |
| Trigger for each row/statement | FOR EACH ROW |
| Trigger WHEN condition | |
| Function | last_updated() |
| Remarks | |
{
"$schema": "http://json-schema.org/draft-04/schema#",
"type": "object",
"title": "city",
"properties": {
"city_id": {
"type": "number",
"default": "nextval('city_city_id_seq'::regclass)"
},
"city": {
"type": "string"
},
"country_id": {
"type": "number"
},
"last_update": {
"type": "string",
"default": "now()"
}
},
"additionalProperties": true,
"required": [
"city_id",
"city",
"country_id",
"last_update"
]
}{
"city_id": "nextval('city_city_id_seq'::regclass)",
"city": "Lorem",
"country_id": 66,
"last_update": "now()"
}CREATE DATABASE dvdrental
ENCODING UTF8
LC_COLLATE 'en_US.UTF-8'
LC_CTYPE 'en_US.UTF-8'
TABLESPACE 'pg_default';
CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;
/*
CREATE TYPE public.mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');
*/
/*
CREATE DOMAIN public.year AS integer
CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));
*/
CREATE TABLE IF NOT EXISTS public.city (
city_id integer DEFAULT nextval('city_city_id_seq'::regclass) NOT NULL,
city varchar(50) NOT NULL,
country_id smallint NOT NULL,
last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
CONSTRAINT city_pkey PRIMARY KEY (city_id),
CONSTRAINT fk_city FOREIGN KEY (country_id) REFERENCES public.country (country_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
) TABLESPACE pg_default;
CREATE TRIGGER last_updated BEFORE UPDATE
ON public.city
FOR EACH ROW
EXECUTE FUNCTION last_updated();
CREATE UNIQUE INDEX IF NOT EXISTS city_pkey
ON ONLY public.city USING BTREE (city_id pg_catalog.int4_ops ASC NULLS LAST) ;
CREATE INDEX IF NOT EXISTS idx_fk_country_id
ON ONLY public.city USING BTREE (country_id pg_catalog.int2_ops ASC NULLS LAST) ;| Property | Value |
| Table | country |
| Technical name | |
| Activated | true |
| Id | |
| Schema | public |
| Additional properties | true |
| $ref | |
| $ref | |
| Comments | |
| Temporary | false |
| Unlogged | false |
| If not exists | true |
| Inherits parent tables | |
| [1] Table name | |
| Partition of | |
| Partition bounds | |
| Partitioning | |
| [1] Partition method | |
| Partition by | |
| Partition key | |
| Expression | |
| Using method | |
| Storage parameters | |
| [1] Fill factor | |
| Parallel workers | |
| Enable autovacuum | |
| Autovacuum params | |
| Enable TOAST autovacuum | |
| TOAST parameters | |
| User catalog table | |
| Tablespace | pg_default |
| As Select statement | |
| Remarks | |
| Column | Type | Req | Key | Description | Comments |
| country_id | integer | true | pk, dk | ||
| country | varchar(50) | true | |||
| last_update | timestamp | true |
| Property | Value |
| Business Name | country_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | integer |
| Array type | |
| Not null | true |
| Default | nextval('country_country_id_seq'::regclass) |
| Comments | |
| Primary key | true |
| Primary key options | |
| [1] Constraint name | country_pkey |
| Include non-key columns | |
| With storage parameters | |
| Tablespace | |
| Comment | |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | country |
| Technical name | |
| Activated | true |
| Id | |
| Type | char |
| Subtype | varchar |
| Length | 50 |
| Array type | |
| Collation rule | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Min length | |
| Max length | |
| Pattern | |
| Format | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | last_update |
| Technical name | |
| Activated | true |
| Id | |
| Type | datetime |
| Subtype | timestamp |
| Precision | |
| Timezone | WITHOUT TIME ZONE |
| Comments | |
| Array type | |
| Not null | true |
| Default | now() |
| Primary key | false |
| Unique | false |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Primary key | |
| [1] Constraint name | |
| Key | |
| Include non-key columns | |
| With storage parameters | |
| Index tablespace | |
| Comment | |
| Unique key | |
| [1] Constraint name | |
| Key | |
| Include non-key columns | |
| With storage parameters | |
| Index tablespace | |
| Comment | |
| Nulls Distinct | |
| Property | |
| Name | country_pkey |
| Activated | true |
| Method | btree |
| Unique | true |
| Nulls Distinct | |
| Concurrent build | |
| If not exist | true |
| Only | true |
| Columns | |
| country_id | |
| Include non-key columns | |
| With storage parameters | |
| [1] Fill factor | |
| Deduplicate items | |
| Tablespace | |
| Where constraint | |
| Comment | |
| Property | last_updated |
| Name | last_updated |
| Description | |
| Or replace | |
| Constraint | |
| Trigger type | BEFORE |
| Trigger events | |
| [1] Event | UPDATE |
| Update columns | |
| Referencing | |
| Trigger for each row/statement | FOR EACH ROW |
| Trigger WHEN condition | |
| Function | last_updated() |
| Remarks | |
{
"$schema": "http://json-schema.org/draft-04/schema#",
"type": "object",
"title": "country",
"properties": {
"country_id": {
"type": "number",
"default": "nextval('country_country_id_seq'::regclass)"
},
"country": {
"type": "string"
},
"last_update": {
"type": "string",
"default": "now()"
}
},
"additionalProperties": true,
"required": [
"country_id",
"country",
"last_update"
]
}{
"country_id": "nextval('country_country_id_seq'::regclass)",
"country": "Lorem",
"last_update": "now()"
}CREATE DATABASE dvdrental
ENCODING UTF8
LC_COLLATE 'en_US.UTF-8'
LC_CTYPE 'en_US.UTF-8'
TABLESPACE 'pg_default';
CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;
/*
CREATE TYPE public.mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');
*/
/*
CREATE DOMAIN public.year AS integer
CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));
*/
CREATE TABLE IF NOT EXISTS public.country (
country_id integer DEFAULT nextval('country_country_id_seq'::regclass) NOT NULL,
country varchar(50) NOT NULL,
last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
CONSTRAINT country_pkey PRIMARY KEY (country_id)
) TABLESPACE pg_default;
CREATE TRIGGER last_updated BEFORE UPDATE
ON public.country
FOR EACH ROW
EXECUTE FUNCTION last_updated();
CREATE UNIQUE INDEX IF NOT EXISTS country_pkey
ON ONLY public.country USING BTREE (country_id pg_catalog.int4_ops ASC NULLS LAST) ;| Property | Value |
| Table | customer |
| Technical name | |
| Activated | true |
| Id | |
| Schema | public |
| Additional properties | true |
| $ref | |
| $ref | |
| Comments | |
| Temporary | false |
| Unlogged | false |
| If not exists | true |
| Inherits parent tables | |
| [1] Table name | |
| Partition of | |
| Partition bounds | |
| Partitioning | |
| [1] Partition method | |
| Partition by | |
| Partition key | |
| Expression | |
| Using method | |
| Storage parameters | |
| [1] Fill factor | |
| Parallel workers | |
| Enable autovacuum | |
| Autovacuum params | |
| Enable TOAST autovacuum | |
| TOAST parameters | |
| User catalog table | |
| Tablespace | pg_default |
| As Select statement | |
| Remarks | |
| Column | Type | Req | Key | Description | Comments |
| customer_id | integer | true | pk, dk | ||
| store_id | smallint | true | |||
| first_name | varchar(45) | true | |||
| last_name | varchar(45) | true | |||
| varchar(50) | false | ||||
| address_id | smallint | true | fk | ||
| activebool | boolean | true | |||
| create_date | date | true | |||
| last_update | timestamp | false | |||
| active | integer | false |
| Property | Value |
| Business Name | customer_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | integer |
| Array type | |
| Not null | true |
| Default | nextval('customer_customer_id_seq'::regclass) |
| Comments | |
| Primary key | true |
| Primary key options | |
| [1] Constraint name | customer_pkey |
| Include non-key columns | |
| With storage parameters | |
| Tablespace | |
| Comment | |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | store_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | smallint |
| Array type | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | first_name |
| Technical name | |
| Activated | true |
| Id | |
| Type | char |
| Subtype | varchar |
| Length | 45 |
| Array type | |
| Collation rule | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Min length | |
| Max length | |
| Pattern | |
| Format | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | last_name |
| Technical name | |
| Activated | true |
| Id | |
| Type | char |
| Subtype | varchar |
| Length | 45 |
| Array type | |
| Collation rule | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Min length | |
| Max length | |
| Pattern | |
| Format | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | |
| Technical name | |
| Activated | true |
| Id | |
| Type | char |
| Subtype | varchar |
| Length | 50 |
| Array type | |
| Collation rule | |
| Not null | false |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Min length | |
| Max length | |
| Pattern | |
| Format | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | address_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | smallint |
| Array type | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | address |
| Foreign field | address_id |
| Relationship type | Foreign Key |
| Relationship name | customer_address_id_fkey |
| Cardinality | 0..n |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | activebool |
| Technical name | |
| Activated | true |
| Id | |
| Type | boolean |
| Comments | |
| Array type | |
| Not null | true |
| Default | true |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | create_date |
| Technical name | |
| Activated | true |
| Id | |
| Type | datetime |
| Subtype | date |
| Comments | |
| Array type | |
| Not null | true |
| Default | ('now'::text)::date |
| Primary key | false |
| Unique | false |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | last_update |
| Technical name | |
| Activated | true |
| Id | |
| Type | datetime |
| Subtype | timestamp |
| Precision | |
| Timezone | WITHOUT TIME ZONE |
| Comments | |
| Array type | |
| Not null | false |
| Default | now() |
| Primary key | false |
| Unique | false |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | active |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | integer |
| Array type | |
| Not null | false |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Primary key | |
| [1] Constraint name | |
| Key | |
| Include non-key columns | |
| With storage parameters | |
| Index tablespace | |
| Comment | |
| Unique key | |
| [1] Constraint name | |
| Key | |
| Include non-key columns | |
| With storage parameters | |
| Index tablespace | |
| Comment | |
| Nulls Distinct | |
| Property | |
| Name | customer_pkey |
| Activated | true |
| Method | btree |
| Unique | true |
| Nulls Distinct | |
| Concurrent build | |
| If not exist | true |
| Only | true |
| Columns | |
| customer_id | |
| Include non-key columns | |
| With storage parameters | |
| [1] Fill factor | |
| Deduplicate items | |
| Tablespace | |
| Where constraint | |
| Comment | |
| Property | |
| Name | idx_fk_address_id |
| Activated | true |
| Method | btree |
| Unique | |
| Concurrent build | |
| If not exist | true |
| Only | true |
| Columns | |
| address_id | |
| Include non-key columns | |
| With storage parameters | |
| [1] Fill factor | |
| Deduplicate items | |
| Tablespace | |
| Where constraint | |
| Comment | |
| Property | |
| Name | idx_fk_store_id |
| Activated | true |
| Method | btree |
| Unique | |
| Concurrent build | |
| If not exist | true |
| Only | true |
| Columns | |
| store_id | |
| Include non-key columns | |
| With storage parameters | |
| [1] Fill factor | |
| Deduplicate items | |
| Tablespace | |
| Where constraint | |
| Comment | |
| Property | |
| Name | idx_last_name |
| Activated | true |
| Method | btree |
| Unique | |
| Concurrent build | |
| If not exist | true |
| Only | true |
| Columns | |
| last_name | |
| Include non-key columns | |
| With storage parameters | |
| [1] Fill factor | |
| Deduplicate items | |
| Tablespace | |
| Where constraint | |
| Comment | |
| Property | last_updated |
| Name | last_updated |
| Description | |
| Or replace | |
| Constraint | |
| Trigger type | BEFORE |
| Trigger events | |
| [1] Event | UPDATE |
| Update columns | |
| Referencing | |
| Trigger for each row/statement | FOR EACH ROW |
| Trigger WHEN condition | |
| Function | last_updated() |
| Remarks | |
{
"$schema": "http://json-schema.org/draft-04/schema#",
"type": "object",
"title": "customer",
"properties": {
"customer_id": {
"type": "number",
"default": "nextval('customer_customer_id_seq'::regclass)"
},
"store_id": {
"type": "number"
},
"first_name": {
"type": "string"
},
"last_name": {
"type": "string"
},
"email": {
"type": "string"
},
"address_id": {
"type": "number"
},
"activebool": {
"type": "boolean",
"default": true
},
"create_date": {
"type": "string",
"default": "('now'::text)::date"
},
"last_update": {
"type": "string",
"default": "now()"
},
"active": {
"type": "number"
}
},
"additionalProperties": true,
"required": [
"customer_id",
"store_id",
"first_name",
"last_name",
"address_id",
"activebool",
"create_date"
]
}{
"customer_id": "nextval('customer_customer_id_seq'::regclass)",
"store_id": -78,
"first_name": "Lorem",
"last_name": "Lorem",
"email": "Lorem",
"address_id": -92,
"activebool": true,
"create_date": "('now'::text)::date",
"last_update": "now()",
"active": -99
}CREATE DATABASE dvdrental
ENCODING UTF8
LC_COLLATE 'en_US.UTF-8'
LC_CTYPE 'en_US.UTF-8'
TABLESPACE 'pg_default';
CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;
/*
CREATE TYPE public.mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');
*/
/*
CREATE DOMAIN public.year AS integer
CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));
*/
CREATE TABLE IF NOT EXISTS public.customer (
customer_id integer DEFAULT nextval('customer_customer_id_seq'::regclass) NOT NULL,
store_id smallint NOT NULL,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
email varchar(50),
address_id smallint NOT NULL,
activebool boolean DEFAULT true NOT NULL,
create_date date DEFAULT E'(\'now\'::text)::date' NOT NULL,
last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()',
active integer,
CONSTRAINT customer_pkey PRIMARY KEY (customer_id),
CONSTRAINT customer_address_id_fkey FOREIGN KEY (address_id) REFERENCES public.address (address_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE
) TABLESPACE pg_default;
CREATE TRIGGER last_updated BEFORE UPDATE
ON public.customer
FOR EACH ROW
EXECUTE FUNCTION last_updated();
CREATE UNIQUE INDEX IF NOT EXISTS customer_pkey
ON ONLY public.customer USING BTREE (customer_id pg_catalog.int4_ops ASC NULLS LAST) ;
CREATE INDEX IF NOT EXISTS idx_fk_address_id
ON ONLY public.customer USING BTREE (address_id pg_catalog.int2_ops ASC NULLS LAST) ;
CREATE INDEX IF NOT EXISTS idx_fk_store_id
ON ONLY public.customer USING BTREE (store_id pg_catalog.int2_ops ASC NULLS LAST) ;
CREATE INDEX IF NOT EXISTS idx_last_name
ON ONLY public.customer USING BTREE (last_name COLLATE pg_catalog."default" pg_catalog.text_ops ASC NULLS LAST) ;| Property | Value |
| Table | film |
| Technical name | |
| Activated | true |
| Id | |
| Schema | public |
| Additional properties | true |
| $ref | |
| $ref | |
| Comments | |
| Temporary | false |
| Unlogged | false |
| If not exists | true |
| Inherits parent tables | |
| [1] Table name | |
| Partition of | |
| Partition bounds | |
| Partitioning | |
| [1] Partition method | |
| Partition by | |
| Partition key | |
| Expression | |
| Using method | |
| Storage parameters | |
| [1] Fill factor | |
| Parallel workers | |
| Enable autovacuum | |
| Enable TOAST autovacuum | |
| User catalog table | |
| Tablespace | pg_default |
| As Select statement | |
| Remarks | |
| Column | Type | Req | Key | Description | Comments |
| film_id | integer | true | pk, dk | ||
| title | varchar(255) | true | |||
| description | text | false | |||
| release_year | domain | false | |||
| language_id | smallint | true | fk | ||
| rental_duration | smallint | true | |||
| rental_rate | numeric(4, 2) | true | |||
| length | smallint | false | |||
| replacement_cost | numeric(5, 2) | true | |||
| rating | enum | false | |||
| last_update | timestamp | true | |||
| special_features | text | false | |||
| fulltext | tsvector | true | |||
| zendesk | varchar(10) | false |
| Property | Value |
| Business Name | film_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | integer |
| Array type | |
| Not null | true |
| Default | nextval('film_film_id_seq'::regclass) |
| Comments | |
| Primary key | true |
| Primary key options | |
| [1] Constraint name | film_pkey |
| Include non-key columns | |
| With storage parameters | |
| Tablespace | |
| Comment | |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | title |
| Technical name | |
| Activated | true |
| Id | |
| Type | char |
| Subtype | varchar |
| Length | 255 |
| Array type | |
| Collation rule | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Min length | |
| Max length | |
| Pattern | |
| Format | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | description |
| Technical name | |
| Activated | true |
| Id | |
| Type | char |
| Subtype | text |
| Array type | |
| Collation rule | |
| Not null | false |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Min length | |
| Max length | |
| Pattern | |
| Format | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | release_year |
| Technical name | |
| Activated | true |
| Reference type | model |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | language_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | smallint |
| Array type | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | language |
| Foreign field | language_id |
| Relationship type | Foreign Key |
| Relationship name | film_language_id_fkey |
| Cardinality | 0..n |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | rental_duration |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | smallint |
| Array type | |
| Not null | true |
| Default | 3 |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | rental_rate |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | numeric |
| Precision | 4 |
| Scale | 2 |
| Array type | |
| Not null | true |
| Default | 4.99 |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | length |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | smallint |
| Array type | |
| Not null | false |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | replacement_cost |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | numeric |
| Precision | 5 |
| Scale | 2 |
| Array type | |
| Not null | true |
| Default | 19.99 |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | rating |
| Technical name | |
| Activated | true |
| Reference type | model |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | last_update |
| Technical name | |
| Activated | true |
| Id | |
| Type | datetime |
| Subtype | timestamp |
| Precision | |
| Timezone | WITHOUT TIME ZONE |
| Comments | |
| Array type | |
| Not null | true |
| Default | now() |
| Primary key | false |
| Unique | false |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | special_features |
| Technical name | |
| Activated | true |
| Id | |
| Type | char |
| Subtype | text |
| Array type | |
| [1] Size limit | |
| Collation rule | |
| Not null | false |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Min length | |
| Max length | |
| Pattern | |
| Format | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | fulltext |
| Technical name | |
| Activated | true |
| Id | |
| Type | char |
| Subtype | tsvector |
| Array type | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Min length | |
| Max length | |
| Pattern | |
| Format | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | zendesk |
| Technical name | |
| Activated | true |
| Id | |
| Type | char |
| Subtype | varchar |
| Length | 10 |
| Array type | |
| Collation rule | |
| Not null | |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Min length | |
| Max length | |
| Pattern | |
| Format | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Primary key | |
| [1] Constraint name | |
| Key | |
| Include non-key columns | |
| With storage parameters | |
| Index tablespace | |
| Comment | |
| Unique key | |
| [1] Constraint name | |
| Key | |
| Include non-key columns | |
| With storage parameters | |
| Index tablespace | |
| Comment | |
| Nulls Distinct | |
| Property | |
| Name | film_fulltext_idx |
| Activated | true |
| Method | gist |
| Concurrent build | |
| If not exist | true |
| Only | true |
| Columns | |
| fulltext | |
| Include non-key columns | |
| With storage parameters | |
| [1] Fill factor | |
| Buffering | |
| Tablespace | |
| Where constraint | |
| Comment | |
| Property | |
| Name | film_pkey |
| Activated | true |
| Method | btree |
| Unique | true |
| Nulls Distinct | |
| Concurrent build | |
| If not exist | true |
| Only | true |
| Columns | |
| film_id | |
| Include non-key columns | |
| With storage parameters | |
| [1] Fill factor | |
| Deduplicate items | |
| Tablespace | |
| Where constraint | |
| Comment | |
| Property | |
| Name | idx_fk_language_id |
| Activated | true |
| Method | btree |
| Unique | |
| Concurrent build | |
| If not exist | true |
| Only | true |
| Columns | |
| language_id | |
| Include non-key columns | |
| With storage parameters | |
| [1] Fill factor | |
| Deduplicate items | |
| Tablespace | |
| Where constraint | |
| Comment | |
| Property | |
| Name | idx_title |
| Activated | true |
| Method | btree |
| Unique | |
| Concurrent build | |
| If not exist | true |
| Only | true |
| Columns | |
| title | |
| Include non-key columns | |
| With storage parameters | |
| [1] Fill factor | |
| Deduplicate items | |
| Tablespace | |
| Where constraint | |
| Comment | |
| Property | film_fulltext_trigger |
| Name | film_fulltext_trigger |
| Description | |
| Or replace | |
| Constraint | |
| Trigger type | BEFORE |
| Trigger events | |
| [1] Event | INSERT |
| [2] Event | UPDATE |
| Update columns | |
| Referencing | |
| Trigger for each row/statement | FOR EACH ROW |
| Trigger WHEN condition | |
| Function | tsvector_update_trigger('fulltext', 'pg_catalog.english', 'title', 'description') |
| Remarks | |
| Property | last_updated |
| Name | last_updated |
| Description | |
| Or replace | |
| Constraint | |
| Trigger type | BEFORE |
| Trigger events | |
| [1] Event | UPDATE |
| Update columns | |
| Referencing | |
| Trigger for each row/statement | FOR EACH ROW |
| Trigger WHEN condition | |
| Function | last_updated() |
| Remarks | |
{
"$schema": "http://json-schema.org/draft-04/schema#",
"type": "object",
"title": "film",
"properties": {
"film_id": {
"type": "number",
"default": "nextval('film_film_id_seq'::regclass)"
},
"title": {
"type": "string"
},
"description": {
"type": "string"
},
"release_year": {
"$ref": "#model/definitions/year"
},
"language_id": {
"type": "number"
},
"rental_duration": {
"type": "number",
"default": "3"
},
"rental_rate": {
"type": "number",
"default": "4.99"
},
"length": {
"type": "number"
},
"replacement_cost": {
"type": "number",
"default": "19.99"
},
"rating": {
"$ref": "#model/definitions/mpaa_rating"
},
"last_update": {
"type": "string",
"default": "now()"
},
"special_features": {
"type": "string"
},
"fulltext": {
"type": "string"
},
"zendesk": {
"type": "string"
}
},
"additionalProperties": true,
"required": [
"film_id",
"title",
"language_id",
"rental_duration",
"rental_rate",
"replacement_cost",
"last_update",
"fulltext"
]
}{
"film_id": "nextval('film_film_id_seq'::regclass)",
"title": "Lorem",
"description": "Lorem",
"release_year": "Lorem",
"language_id": -4,
"rental_duration": "3",
"rental_rate": "4.99",
"length": 12,
"replacement_cost": "19.99",
"rating": "NC-17",
"last_update": "now()",
"special_features": "Lorem",
"fulltext": "Lorem",
"zendesk": "Lorem"
}CREATE DATABASE dvdrental
ENCODING UTF8
LC_COLLATE 'en_US.UTF-8'
LC_CTYPE 'en_US.UTF-8'
TABLESPACE 'pg_default';
CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;
CREATE TYPE public.mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');
CREATE DOMAIN public.year AS integer
CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));
CREATE TABLE IF NOT EXISTS public.film (
film_id integer DEFAULT nextval('film_film_id_seq'::regclass) NOT NULL,
title varchar(255) NOT NULL,
description text,
release_year year,
language_id smallint NOT NULL,
rental_duration smallint DEFAULT 3 NOT NULL,
rental_rate numeric(4,2) DEFAULT 4.99 NOT NULL,
length smallint,
replacement_cost numeric(5,2) DEFAULT 19.99 NOT NULL,
rating mpaa_rating,
last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
special_features text[],
fulltext tsvector NOT NULL,
zendesk varchar(10),
CONSTRAINT film_pkey PRIMARY KEY (film_id),
CONSTRAINT film_language_id_fkey FOREIGN KEY (language_id) REFERENCES public.language (language_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE
) TABLESPACE pg_default;
CREATE TRIGGER film_fulltext_trigger BEFORE INSERT OR UPDATE
ON public.film
FOR EACH ROW
EXECUTE FUNCTION tsvector_update_trigger('fulltext', 'pg_catalog.english', 'title', 'description');
CREATE TRIGGER last_updated BEFORE UPDATE
ON public.film
FOR EACH ROW
EXECUTE FUNCTION last_updated();
CREATE INDEX IF NOT EXISTS film_fulltext_idx
ON ONLY public.film USING GIST (fulltext pg_catalog.tsvector_ops) ;
CREATE UNIQUE INDEX IF NOT EXISTS film_pkey
ON ONLY public.film USING BTREE (film_id pg_catalog.int4_ops ASC NULLS LAST) ;
CREATE INDEX IF NOT EXISTS idx_fk_language_id
ON ONLY public.film USING BTREE (language_id pg_catalog.int2_ops ASC NULLS LAST) ;
CREATE INDEX IF NOT EXISTS idx_title
ON ONLY public.film USING BTREE (title COLLATE pg_catalog."default" pg_catalog.text_ops ASC NULLS LAST) ;| Property | Value |
| Table | film_actor |
| Technical name | |
| Activated | true |
| Id | |
| Schema | public |
| Additional properties | true |
| $ref | |
| $ref | |
| Comments | |
| Temporary | false |
| Unlogged | false |
| If not exists | true |
| Inherits parent tables | |
| [1] Table name | |
| Partition of | |
| Partition bounds | |
| Partitioning | |
| [1] Partition method | |
| Partition by | |
| Partition key | |
| Expression | |
| Using method | |
| Storage parameters | |
| [1] Fill factor | |
| Parallel workers | |
| Enable autovacuum | |
| Autovacuum params | |
| Enable TOAST autovacuum | |
| TOAST parameters | |
| User catalog table | |
| Tablespace | pg_default |
| As Select statement | |
| Remarks | |
| Column | Type | Req | Key | Description | Comments |
| actor_id | smallint | true | pk, fk | ||
| film_id | smallint | true | pk, fk | ||
| last_update | timestamp | true |
| Property | Value |
| Business Name | actor_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | smallint |
| Array type | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | true |
| Foreign table | actor |
| Foreign field | actor_id |
| Relationship type | Foreign Key |
| Relationship name | film_actor_actor_id_fkey |
| Cardinality | 0..n |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | film_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | smallint |
| Array type | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | true |
| Foreign table | film |
| Foreign field | film_id |
| Relationship type | Foreign Key |
| Relationship name | film_actor_film_id_fkey |
| Cardinality | 0..n |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | last_update |
| Technical name | |
| Activated | true |
| Id | |
| Type | datetime |
| Subtype | timestamp |
| Precision | |
| Timezone | WITHOUT TIME ZONE |
| Comments | |
| Array type | |
| Not null | true |
| Default | now() |
| Primary key | false |
| Unique | false |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Primary key | |
| [1] Constraint name | film_actor_pkey |
| Key | |
| actor_id | |
| film_id | |
| Include non-key columns | |
| With storage parameters | |
| Index tablespace | |
| Comment | |
| Unique key | |
| [1] Constraint name | |
| Key | |
| Include non-key columns | |
| With storage parameters | |
| Index tablespace | |
| Comment | |
| Nulls Distinct | |
| Property | |
| Name | film_actor_pkey |
| Activated | true |
| Method | btree |
| Unique | true |
| Nulls Distinct | |
| Concurrent build | |
| If not exist | true |
| Only | true |
| Columns | |
| actor_id | |
| film_id | |
| Include non-key columns | |
| With storage parameters | |
| [1] Fill factor | |
| Deduplicate items | |
| Tablespace | |
| Where constraint | |
| Comment | |
| Property | |
| Name | idx_fk_film_id |
| Activated | true |
| Method | btree |
| Unique | |
| Concurrent build | |
| If not exist | true |
| Only | true |
| Columns | |
| film_id | |
| Include non-key columns | |
| With storage parameters | |
| [1] Fill factor | |
| Deduplicate items | |
| Tablespace | |
| Where constraint | |
| Comment | |
| Property | last_updated |
| Name | last_updated |
| Description | |
| Or replace | |
| Constraint | |
| Trigger type | BEFORE |
| Trigger events | |
| [1] Event | UPDATE |
| Update columns | |
| Referencing | |
| Trigger for each row/statement | FOR EACH ROW |
| Trigger WHEN condition | |
| Function | last_updated() |
| Remarks | |
{
"$schema": "http://json-schema.org/draft-04/schema#",
"type": "object",
"title": "film_actor",
"properties": {
"actor_id": {
"type": "number"
},
"film_id": {
"type": "number"
},
"last_update": {
"type": "string",
"default": "now()"
}
},
"additionalProperties": true,
"required": [
"actor_id",
"film_id",
"last_update"
]
}{
"actor_id": 58,
"film_id": -83,
"last_update": "now()"
}CREATE DATABASE dvdrental
ENCODING UTF8
LC_COLLATE 'en_US.UTF-8'
LC_CTYPE 'en_US.UTF-8'
TABLESPACE 'pg_default';
CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;
/*
CREATE TYPE public.mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');
*/
/*
CREATE DOMAIN public.year AS integer
CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));
*/
CREATE TABLE IF NOT EXISTS public.film_actor (
actor_id smallint NOT NULL,
film_id smallint NOT NULL,
last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
CONSTRAINT film_actor_pkey PRIMARY KEY (actor_id, film_id),
CONSTRAINT film_actor_actor_id_fkey FOREIGN KEY (actor_id) REFERENCES public.actor (actor_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT film_actor_film_id_fkey FOREIGN KEY (film_id) REFERENCES public.film (film_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE
) TABLESPACE pg_default;
CREATE TRIGGER last_updated BEFORE UPDATE
ON public.film_actor
FOR EACH ROW
EXECUTE FUNCTION last_updated();
CREATE UNIQUE INDEX IF NOT EXISTS film_actor_pkey
ON ONLY public.film_actor USING BTREE (actor_id pg_catalog.int2_ops ASC NULLS LAST, film_id pg_catalog.int2_ops ASC NULLS LAST) ;
CREATE INDEX IF NOT EXISTS idx_fk_film_id
ON ONLY public.film_actor USING BTREE (film_id pg_catalog.int2_ops ASC NULLS LAST) ;| Property | Value |
| Table | film_category |
| Technical name | |
| Activated | true |
| Id | |
| Schema | public |
| Additional properties | true |
| $ref | |
| $ref | |
| Comments | |
| Temporary | false |
| Unlogged | false |
| If not exists | true |
| Inherits parent tables | |
| [1] Table name | |
| Partition of | |
| Partition bounds | |
| Partitioning | |
| [1] Partition method | |
| Partition by | |
| Partition key | |
| Expression | |
| Using method | |
| Storage parameters | |
| [1] Fill factor | |
| Parallel workers | |
| Enable autovacuum | |
| Autovacuum params | |
| Enable TOAST autovacuum | |
| TOAST parameters | |
| User catalog table | |
| Tablespace | pg_default |
| As Select statement | |
| Remarks | |
| Column | Type | Req | Key | Description | Comments |
| film_id | smallint | true | pk, fk | ||
| category_id | smallint | true | pk, fk | ||
| last_update | timestamp | true |
| Property | Value |
| Business Name | film_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | smallint |
| Array type | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | true |
| Foreign table | film |
| Foreign field | film_id |
| Relationship type | Foreign Key |
| Relationship name | film_category_film_id_fkey |
| Cardinality | 0..n |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | category_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | smallint |
| Array type | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | true |
| Foreign table | category |
| Foreign field | category_id |
| Relationship type | Foreign Key |
| Relationship name | film_category_category_id_fkey |
| Cardinality | 0..n |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | last_update |
| Technical name | |
| Activated | true |
| Id | |
| Type | datetime |
| Subtype | timestamp |
| Precision | |
| Timezone | WITHOUT TIME ZONE |
| Comments | |
| Array type | |
| Not null | true |
| Default | now() |
| Primary key | false |
| Unique | false |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Primary key | |
| [1] Constraint name | film_category_pkey |
| Key | |
| film_id | |
| category_id | |
| Include non-key columns | |
| With storage parameters | |
| Index tablespace | |
| Comment | |
| Unique key | |
| [1] Constraint name | |
| Key | |
| Include non-key columns | |
| With storage parameters | |
| Index tablespace | |
| Comment | |
| Nulls Distinct | |
| Property | |
| Name | film_category_pkey |
| Activated | true |
| Method | btree |
| Unique | true |
| Nulls Distinct | |
| Concurrent build | |
| If not exist | true |
| Only | true |
| Columns | |
| film_id | |
| category_id | |
| Include non-key columns | |
| With storage parameters | |
| [1] Fill factor | |
| Deduplicate items | |
| Tablespace | |
| Where constraint | |
| Comment | |
| Property | last_updated |
| Name | last_updated |
| Description | |
| Or replace | |
| Constraint | |
| Trigger type | BEFORE |
| Trigger events | |
| [1] Event | UPDATE |
| Update columns | |
| Referencing | |
| Trigger for each row/statement | FOR EACH ROW |
| Trigger WHEN condition | |
| Function | last_updated() |
| Remarks | |
{
"$schema": "http://json-schema.org/draft-04/schema#",
"type": "object",
"title": "film_category",
"properties": {
"film_id": {
"type": "number"
},
"category_id": {
"type": "number"
},
"last_update": {
"type": "string",
"default": "now()"
}
},
"additionalProperties": true,
"required": [
"film_id",
"category_id",
"last_update"
]
}{
"film_id": -10,
"category_id": 32,
"last_update": "now()"
}CREATE DATABASE dvdrental
ENCODING UTF8
LC_COLLATE 'en_US.UTF-8'
LC_CTYPE 'en_US.UTF-8'
TABLESPACE 'pg_default';
CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;
/*
CREATE TYPE public.mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');
*/
/*
CREATE DOMAIN public.year AS integer
CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));
*/
CREATE TABLE IF NOT EXISTS public.film_category (
film_id smallint NOT NULL,
category_id smallint NOT NULL,
last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
CONSTRAINT film_category_pkey PRIMARY KEY (film_id, category_id),
CONSTRAINT film_category_category_id_fkey FOREIGN KEY (category_id) REFERENCES public.category (category_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT film_category_film_id_fkey FOREIGN KEY (film_id) REFERENCES public.film (film_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE
) TABLESPACE pg_default;
CREATE TRIGGER last_updated BEFORE UPDATE
ON public.film_category
FOR EACH ROW
EXECUTE FUNCTION last_updated();
CREATE UNIQUE INDEX IF NOT EXISTS film_category_pkey
ON ONLY public.film_category USING BTREE (film_id pg_catalog.int2_ops ASC NULLS LAST, category_id pg_catalog.int2_ops ASC NULLS LAST) ;| Property | Value |
| Table | inventory |
| Technical name | |
| Activated | true |
| Id | |
| Schema | public |
| Additional properties | true |
| $ref | |
| $ref | |
| Comments | |
| Temporary | false |
| Unlogged | false |
| If not exists | true |
| Inherits parent tables | |
| [1] Table name | |
| Partition of | |
| Partition bounds | |
| Partitioning | |
| [1] Partition method | |
| Partition by | |
| Partition key | |
| Expression | |
| Using method | |
| Storage parameters | |
| [1] Fill factor | |
| Parallel workers | |
| Enable autovacuum | |
| Autovacuum params | |
| Enable TOAST autovacuum | |
| TOAST parameters | |
| User catalog table | |
| Tablespace | pg_default |
| As Select statement | |
| Remarks | |
| Column | Type | Req | Key | Description | Comments |
| inventory_id | integer | true | pk, dk | ||
| film_id | smallint | true | fk | ||
| store_id | smallint | true | |||
| last_update | timestamp | true |
| Property | Value |
| Business Name | inventory_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | integer |
| Array type | |
| Not null | true |
| Default | nextval('inventory_inventory_id_seq'::regclass) |
| Comments | |
| Primary key | true |
| Primary key options | |
| [1] Constraint name | inventory_pkey |
| Include non-key columns | |
| With storage parameters | |
| Tablespace | |
| Comment | |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | film_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | smallint |
| Array type | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | film |
| Foreign field | film_id |
| Relationship type | Foreign Key |
| Relationship name | inventory_film_id_fkey |
| Cardinality | 0..n |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | store_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | smallint |
| Array type | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | last_update |
| Technical name | |
| Activated | true |
| Id | |
| Type | datetime |
| Subtype | timestamp |
| Precision | |
| Timezone | WITHOUT TIME ZONE |
| Comments | |
| Array type | |
| Not null | true |
| Default | now() |
| Primary key | false |
| Unique | false |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Primary key | |
| [1] Constraint name | |
| Key | |
| Include non-key columns | |
| With storage parameters | |
| Index tablespace | |
| Comment | |
| Unique key | |
| [1] Constraint name | |
| Key | |
| Include non-key columns | |
| With storage parameters | |
| Index tablespace | |
| Comment | |
| Nulls Distinct | |
| Property | |
| Name | idx_store_id_film_id |
| Activated | true |
| Method | btree |
| Unique | |
| Concurrent build | |
| If not exist | true |
| Only | true |
| Columns | |
| store_id | |
| film_id | |
| Include non-key columns | |
| With storage parameters | |
| [1] Fill factor | |
| Deduplicate items | |
| Tablespace | |
| Where constraint | |
| Comment | |
| Property | |
| Name | inventory_pkey |
| Activated | true |
| Method | btree |
| Unique | true |
| Nulls Distinct | |
| Concurrent build | |
| If not exist | true |
| Only | true |
| Columns | |
| inventory_id | |
| Include non-key columns | |
| With storage parameters | |
| [1] Fill factor | |
| Deduplicate items | |
| Tablespace | |
| Where constraint | |
| Comment | |
| Property | last_updated |
| Name | last_updated |
| Description | |
| Or replace | |
| Constraint | |
| Trigger type | BEFORE |
| Trigger events | |
| [1] Event | UPDATE |
| Update columns | |
| Referencing | |
| Trigger for each row/statement | FOR EACH ROW |
| Trigger WHEN condition | |
| Function | last_updated() |
| Remarks | |
{
"$schema": "http://json-schema.org/draft-04/schema#",
"type": "object",
"title": "inventory",
"properties": {
"inventory_id": {
"type": "number",
"default": "nextval('inventory_inventory_id_seq'::regclass)"
},
"film_id": {
"type": "number"
},
"store_id": {
"type": "number"
},
"last_update": {
"type": "string",
"default": "now()"
}
},
"additionalProperties": true,
"required": [
"inventory_id",
"film_id",
"store_id",
"last_update"
]
}{
"inventory_id": "nextval('inventory_inventory_id_seq'::regclass)",
"film_id": -12,
"store_id": 81,
"last_update": "now()"
}CREATE DATABASE dvdrental
ENCODING UTF8
LC_COLLATE 'en_US.UTF-8'
LC_CTYPE 'en_US.UTF-8'
TABLESPACE 'pg_default';
CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;
/*
CREATE TYPE public.mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');
*/
/*
CREATE DOMAIN public.year AS integer
CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));
*/
CREATE TABLE IF NOT EXISTS public.inventory (
inventory_id integer DEFAULT nextval('inventory_inventory_id_seq'::regclass) NOT NULL,
film_id smallint NOT NULL,
store_id smallint NOT NULL,
last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
CONSTRAINT inventory_pkey PRIMARY KEY (inventory_id),
CONSTRAINT inventory_film_id_fkey FOREIGN KEY (film_id) REFERENCES public.film (film_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE
) TABLESPACE pg_default;
CREATE TRIGGER last_updated BEFORE UPDATE
ON public.inventory
FOR EACH ROW
EXECUTE FUNCTION last_updated();
CREATE INDEX IF NOT EXISTS idx_store_id_film_id
ON ONLY public.inventory USING BTREE (store_id pg_catalog.int2_ops ASC NULLS LAST, film_id pg_catalog.int2_ops ASC NULLS LAST) ;
CREATE UNIQUE INDEX IF NOT EXISTS inventory_pkey
ON ONLY public.inventory USING BTREE (inventory_id pg_catalog.int4_ops ASC NULLS LAST) ;| Property | Value |
| Table | language |
| Technical name | |
| Activated | true |
| Id | |
| Schema | public |
| Additional properties | true |
| $ref | |
| $ref | |
| Comments | |
| Temporary | false |
| Unlogged | false |
| If not exists | true |
| Inherits parent tables | |
| [1] Table name | |
| Partition of | |
| Partition bounds | |
| Partitioning | |
| [1] Partition method | |
| Partition by | |
| Partition key | |
| Expression | |
| Using method | |
| Storage parameters | |
| [1] Fill factor | |
| Parallel workers | |
| Enable autovacuum | |
| Autovacuum params | |
| Enable TOAST autovacuum | |
| TOAST parameters | |
| User catalog table | |
| Tablespace | pg_default |
| As Select statement | |
| Remarks | |
| Column | Type | Req | Key | Description | Comments |
| language_id | integer | true | pk, dk | ||
| name | char(20) | true | |||
| last_update | timestamp | true |
| Property | Value |
| Business Name | language_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | integer |
| Array type | |
| Not null | true |
| Default | nextval('language_language_id_seq'::regclass) |
| Comments | |
| Primary key | true |
| Primary key options | |
| [1] Constraint name | language_pkey |
| Include non-key columns | |
| With storage parameters | |
| Tablespace | |
| Comment | |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | name |
| Technical name | |
| Activated | true |
| Id | |
| Type | char |
| Subtype | char |
| Length | 20 |
| Array type | |
| Collation rule | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Min length | |
| Max length | |
| Pattern | |
| Format | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | last_update |
| Technical name | |
| Activated | true |
| Id | |
| Type | datetime |
| Subtype | timestamp |
| Precision | |
| Timezone | WITHOUT TIME ZONE |
| Comments | |
| Array type | |
| Not null | true |
| Default | now() |
| Primary key | false |
| Unique | false |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Primary key | |
| [1] Constraint name | |
| Key | |
| Include non-key columns | |
| With storage parameters | |
| Index tablespace | |
| Comment | |
| Unique key | |
| [1] Constraint name | |
| Key | |
| Include non-key columns | |
| With storage parameters | |
| Index tablespace | |
| Comment | |
| Nulls Distinct | |
| Property | |
| Name | language_pkey |
| Activated | true |
| Method | btree |
| Unique | true |
| Nulls Distinct | |
| Concurrent build | |
| If not exist | true |
| Only | true |
| Columns | |
| language_id | |
| Include non-key columns | |
| With storage parameters | |
| [1] Fill factor | |
| Deduplicate items | |
| Tablespace | |
| Where constraint | |
| Comment | |
| Property | last_updated |
| Name | last_updated |
| Description | |
| Or replace | |
| Constraint | |
| Trigger type | BEFORE |
| Trigger events | |
| [1] Event | UPDATE |
| Update columns | |
| Referencing | |
| Trigger for each row/statement | FOR EACH ROW |
| Trigger WHEN condition | |
| Function | last_updated() |
| Remarks | |
{
"$schema": "http://json-schema.org/draft-04/schema#",
"type": "object",
"title": "language",
"properties": {
"language_id": {
"type": "number",
"default": "nextval('language_language_id_seq'::regclass)"
},
"name": {
"type": "string"
},
"last_update": {
"type": "string",
"default": "now()"
}
},
"additionalProperties": true,
"required": [
"language_id",
"name",
"last_update"
]
}{
"language_id": "nextval('language_language_id_seq'::regclass)",
"name": "Lorem",
"last_update": "now()"
}CREATE DATABASE dvdrental
ENCODING UTF8
LC_COLLATE 'en_US.UTF-8'
LC_CTYPE 'en_US.UTF-8'
TABLESPACE 'pg_default';
CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;
/*
CREATE TYPE public.mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');
*/
/*
CREATE DOMAIN public.year AS integer
CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));
*/
CREATE TABLE IF NOT EXISTS public.language (
language_id integer DEFAULT nextval('language_language_id_seq'::regclass) NOT NULL,
name char(20) NOT NULL,
last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
CONSTRAINT language_pkey PRIMARY KEY (language_id)
) TABLESPACE pg_default;
CREATE TRIGGER last_updated BEFORE UPDATE
ON public.language
FOR EACH ROW
EXECUTE FUNCTION last_updated();
CREATE UNIQUE INDEX IF NOT EXISTS language_pkey
ON ONLY public.language USING BTREE (language_id pg_catalog.int4_ops ASC NULLS LAST) ;| Property | Value |
| Table | payment |
| Technical name | |
| Activated | true |
| Id | |
| Schema | public |
| Additional properties | true |
| $ref | |
| $ref | |
| Comments | |
| Temporary | false |
| Unlogged | false |
| If not exists | true |
| Inherits parent tables | |
| [1] Table name | |
| Partition of | |
| Partition bounds | |
| Partitioning | |
| [1] Partition method | |
| Partition by | |
| Partition key | |
| Expression | |
| Using method | |
| Storage parameters | |
| [1] Fill factor | |
| Parallel workers | |
| Enable autovacuum | |
| Autovacuum params | |
| Enable TOAST autovacuum | |
| TOAST parameters | |
| User catalog table | |
| Tablespace | pg_default |
| As Select statement | |
| Remarks | |
| Column | Type | Req | Key | Description | Comments |
| payment_id | integer | true | pk | ||
| customer_id | smallint | true | fk | ||
| staff_id | smallint | true | fk | ||
| rental_id | integer | true | fk | ||
| amount | numeric(5, 2) | true | |||
| payment_date | timestamp | true |
| Property | Value |
| Business Name | payment_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | integer |
| Array type | |
| Not null | true |
| Default | nextval('payment_payment_id_seq'::regclass) |
| Comments | |
| Primary key | true |
| Primary key options | |
| [1] Constraint name | payment_pkey |
| Include non-key columns | |
| With storage parameters | |
| Tablespace | |
| Comment | |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | customer_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | smallint |
| Array type | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | customer |
| Foreign field | customer_id |
| Relationship type | Foreign Key |
| Relationship name | payment_customer_id_fkey |
| Cardinality | 0..n |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | staff_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | smallint |
| Array type | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | staff |
| Foreign field | staff_id |
| Relationship type | Foreign Key |
| Relationship name | payment_staff_id_fkey |
| Cardinality | 0..n |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | rental_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | integer |
| Array type | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | rental |
| Foreign field | rental_id |
| Relationship type | Foreign Key |
| Relationship name | payment_rental_id_fkey |
| Cardinality | 0..n |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | amount |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | numeric |
| Precision | 5 |
| Scale | 2 |
| Array type | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | payment_date |
| Technical name | |
| Activated | true |
| Id | |
| Type | datetime |
| Subtype | timestamp |
| Precision | |
| Timezone | WITHOUT TIME ZONE |
| Comments | |
| Array type | |
| Not null | true |
| Default | |
| Primary key | false |
| Unique | false |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Primary key | |
| [1] Constraint name | |
| Key | |
| Include non-key columns | |
| With storage parameters | |
| Index tablespace | |
| Comment | |
| Unique key | |
| [1] Constraint name | |
| Key | |
| Include non-key columns | |
| With storage parameters | |
| Index tablespace | |
| Comment | |
| Nulls Distinct | |
| Property | |
| Name | idx_fk_customer_id |
| Activated | true |
| Method | btree |
| Unique | |
| Concurrent build | |
| If not exist | true |
| Only | true |
| Columns | |
| customer_id | |
| Include non-key columns | |
| With storage parameters | |
| [1] Fill factor | |
| Deduplicate items | |
| Tablespace | |
| Where constraint | |
| Comment | |
| Property | |
| Name | idx_fk_rental_id |
| Activated | true |
| Method | btree |
| Unique | |
| Concurrent build | |
| If not exist | true |
| Only | true |
| Columns | |
| rental_id | |
| Include non-key columns | |
| With storage parameters | |
| [1] Fill factor | |
| Deduplicate items | |
| Tablespace | |
| Where constraint | |
| Comment | |
| Property | |
| Name | idx_fk_staff_id |
| Activated | true |
| Method | btree |
| Unique | |
| Concurrent build | |
| If not exist | true |
| Only | true |
| Columns | |
| staff_id | |
| Include non-key columns | |
| With storage parameters | |
| [1] Fill factor | |
| Deduplicate items | |
| Tablespace | |
| Where constraint | |
| Comment | |
| Property | |
| Name | payment_pkey |
| Activated | true |
| Method | btree |
| Unique | true |
| Nulls Distinct | |
| Concurrent build | |
| If not exist | true |
| Only | true |
| Columns | |
| payment_id | |
| Include non-key columns | |
| With storage parameters | |
| [1] Fill factor | |
| Deduplicate items | |
| Tablespace | |
| Where constraint | |
| Comment | |
{
"$schema": "http://json-schema.org/draft-04/schema#",
"type": "object",
"title": "payment",
"properties": {
"payment_id": {
"type": "number",
"default": "nextval('payment_payment_id_seq'::regclass)"
},
"customer_id": {
"type": "number"
},
"staff_id": {
"type": "number"
},
"rental_id": {
"type": "number"
},
"amount": {
"type": "number"
},
"payment_date": {
"type": "string"
}
},
"additionalProperties": true,
"required": [
"payment_id",
"customer_id",
"staff_id",
"rental_id",
"amount",
"payment_date"
]
}{
"payment_id": "nextval('payment_payment_id_seq'::regclass)",
"customer_id": -51,
"staff_id": 84,
"rental_id": 9,
"amount": -14,
"payment_date": "2011-02-03 04:05:00+0000"
}CREATE DATABASE dvdrental
ENCODING UTF8
LC_COLLATE 'en_US.UTF-8'
LC_CTYPE 'en_US.UTF-8'
TABLESPACE 'pg_default';
CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;
/*
CREATE TYPE public.mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');
*/
/*
CREATE DOMAIN public.year AS integer
CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));
*/
CREATE TABLE IF NOT EXISTS public.payment (
payment_id integer DEFAULT nextval('payment_payment_id_seq'::regclass) NOT NULL,
customer_id smallint NOT NULL,
staff_id smallint NOT NULL,
rental_id integer NOT NULL,
amount numeric(5,2) NOT NULL,
payment_date timestamp WITHOUT TIME ZONE NOT NULL,
CONSTRAINT payment_pkey PRIMARY KEY (payment_id),
CONSTRAINT payment_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES public.customer (customer_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT payment_rental_id_fkey FOREIGN KEY (rental_id) REFERENCES public.rental (rental_id) MATCH SIMPLE ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT payment_staff_id_fkey FOREIGN KEY (staff_id) REFERENCES public.staff (staff_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE
) TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS idx_fk_customer_id
ON ONLY public.payment USING BTREE (customer_id pg_catalog.int2_ops ASC NULLS LAST) ;
CREATE INDEX IF NOT EXISTS idx_fk_rental_id
ON ONLY public.payment USING BTREE (rental_id pg_catalog.int4_ops ASC NULLS LAST) ;
CREATE INDEX IF NOT EXISTS idx_fk_staff_id
ON ONLY public.payment USING BTREE (staff_id pg_catalog.int2_ops ASC NULLS LAST) ;
CREATE UNIQUE INDEX IF NOT EXISTS payment_pkey
ON ONLY public.payment USING BTREE (payment_id pg_catalog.int4_ops ASC NULLS LAST) ;| Property | Value |
| Table | rental |
| Technical name | |
| Activated | true |
| Id | |
| Schema | public |
| Additional properties | true |
| $ref | |
| $ref | |
| Comments | |
| Temporary | false |
| Unlogged | false |
| If not exists | true |
| Inherits parent tables | |
| [1] Table name | |
| Partition of | |
| Partition bounds | |
| Partitioning | |
| [1] Partition method | |
| Partition by | |
| Partition key | |
| Expression | |
| Using method | |
| Storage parameters | |
| [1] Fill factor | |
| Parallel workers | |
| Enable autovacuum | |
| Autovacuum params | |
| Enable TOAST autovacuum | |
| TOAST parameters | |
| User catalog table | |
| Tablespace | pg_default |
| As Select statement | |
| Remarks | |
| Column | Type | Req | Key | Description | Comments |
| rental_id | integer | true | pk, dk | ||
| rental_date | timestamp | true | |||
| inventory_id | integer | true | fk | ||
| customer_id | smallint | true | fk | ||
| return_date | timestamp | false | |||
| staff_id | smallint | true | fk | ||
| last_update | timestamp | true |
| Property | Value |
| Business Name | rental_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | integer |
| Array type | |
| Not null | true |
| Default | nextval('rental_rental_id_seq'::regclass) |
| Comments | |
| Primary key | true |
| Primary key options | |
| [1] Constraint name | rental_pkey |
| Include non-key columns | |
| With storage parameters | |
| Tablespace | |
| Comment | |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | rental_date |
| Technical name | |
| Activated | true |
| Id | |
| Type | datetime |
| Subtype | timestamp |
| Precision | |
| Timezone | WITHOUT TIME ZONE |
| Comments | |
| Array type | |
| Not null | true |
| Default | |
| Primary key | false |
| Unique | false |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | inventory_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | integer |
| Array type | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | inventory |
| Foreign field | inventory_id |
| Relationship type | Foreign Key |
| Relationship name | rental_inventory_id_fkey |
| Cardinality | 0..n |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | customer_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | smallint |
| Array type | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | customer |
| Foreign field | customer_id |
| Relationship type | Foreign Key |
| Relationship name | rental_customer_id_fkey |
| Cardinality | 0..n |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | return_date |
| Technical name | |
| Activated | true |
| Id | |
| Type | datetime |
| Subtype | timestamp |
| Precision | |
| Timezone | WITHOUT TIME ZONE |
| Comments | |
| Array type | |
| Not null | false |
| Default | |
| Primary key | false |
| Unique | false |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | staff_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | smallint |
| Array type | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | staff |
| Foreign field | staff_id |
| Relationship type | Foreign Key |
| Relationship name | rental_staff_id_key |
| Cardinality | 0..n |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | last_update |
| Technical name | |
| Activated | true |
| Id | |
| Type | datetime |
| Subtype | timestamp |
| Precision | |
| Timezone | WITHOUT TIME ZONE |
| Comments | |
| Array type | |
| Not null | true |
| Default | now() |
| Primary key | false |
| Unique | false |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Primary key | |
| [1] Constraint name | |
| Key | |
| Include non-key columns | |
| With storage parameters | |
| Index tablespace | |
| Comment | |
| Unique key | |
| [1] Constraint name | |
| Key | |
| Include non-key columns | |
| With storage parameters | |
| Index tablespace | |
| Comment | |
| Nulls Distinct | |
| Property | |
| Name | idx_fk_inventory_id |
| Activated | true |
| Method | btree |
| Unique | |
| Concurrent build | |
| If not exist | true |
| Only | true |
| Columns | |
| inventory_id | |
| Include non-key columns | |
| With storage parameters | |
| [1] Fill factor | |
| Deduplicate items | |
| Tablespace | |
| Where constraint | |
| Comment | |
| Property | |
| Name | idx_unq_rental_rental_date_inventory_id_customer_id |
| Activated | true |
| Method | btree |
| Unique | true |
| Nulls Distinct | |
| Concurrent build | |
| If not exist | true |
| Only | true |
| Columns | |
| rental_date | |
| inventory_id | |
| customer_id | |
| Include non-key columns | |
| With storage parameters | |
| [1] Fill factor | |
| Deduplicate items | |
| Tablespace | |
| Where constraint | |
| Comment | |
| Property | |
| Name | rental_pkey |
| Activated | true |
| Method | btree |
| Unique | true |
| Nulls Distinct | |
| Concurrent build | |
| If not exist | true |
| Only | true |
| Columns | |
| rental_id | |
| Include non-key columns | |
| With storage parameters | |
| [1] Fill factor | |
| Deduplicate items | |
| Tablespace | |
| Where constraint | |
| Comment | |
| Property | last_updated |
| Name | last_updated |
| Description | |
| Or replace | |
| Constraint | |
| Trigger type | BEFORE |
| Trigger events | |
| [1] Event | UPDATE |
| Update columns | |
| Referencing | |
| Trigger for each row/statement | FOR EACH ROW |
| Trigger WHEN condition | |
| Function | last_updated() |
| Remarks | |
{
"$schema": "http://json-schema.org/draft-04/schema#",
"type": "object",
"title": "rental",
"properties": {
"rental_id": {
"type": "number",
"default": "nextval('rental_rental_id_seq'::regclass)"
},
"rental_date": {
"type": "string"
},
"inventory_id": {
"type": "number"
},
"customer_id": {
"type": "number"
},
"return_date": {
"type": "string"
},
"staff_id": {
"type": "number"
},
"last_update": {
"type": "string",
"default": "now()"
}
},
"additionalProperties": true,
"required": [
"rental_id",
"rental_date",
"inventory_id",
"customer_id",
"staff_id",
"last_update"
]
}{
"rental_id": "nextval('rental_rental_id_seq'::regclass)",
"rental_date": "2011-02-03 04:05:00+0000",
"inventory_id": 57,
"customer_id": -58,
"return_date": "2011-02-03 04:05:00+0000",
"staff_id": -63,
"last_update": "now()"
}CREATE DATABASE dvdrental
ENCODING UTF8
LC_COLLATE 'en_US.UTF-8'
LC_CTYPE 'en_US.UTF-8'
TABLESPACE 'pg_default';
CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;
/*
CREATE TYPE public.mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');
*/
/*
CREATE DOMAIN public.year AS integer
CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));
*/
CREATE TABLE IF NOT EXISTS public.rental (
rental_id integer DEFAULT nextval('rental_rental_id_seq'::regclass) NOT NULL,
rental_date timestamp WITHOUT TIME ZONE NOT NULL,
inventory_id integer NOT NULL,
customer_id smallint NOT NULL,
return_date timestamp WITHOUT TIME ZONE,
staff_id smallint NOT NULL,
last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
CONSTRAINT rental_pkey PRIMARY KEY (rental_id),
CONSTRAINT rental_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES public.customer (customer_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT rental_inventory_id_fkey FOREIGN KEY (inventory_id) REFERENCES public.inventory (inventory_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT rental_staff_id_key FOREIGN KEY (staff_id) REFERENCES public.staff (staff_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
) TABLESPACE pg_default;
CREATE TRIGGER last_updated BEFORE UPDATE
ON public.rental
FOR EACH ROW
EXECUTE FUNCTION last_updated();
CREATE INDEX IF NOT EXISTS idx_fk_inventory_id
ON ONLY public.rental USING BTREE (inventory_id pg_catalog.int4_ops ASC NULLS LAST) ;
CREATE UNIQUE INDEX IF NOT EXISTS idx_unq_rental_rental_date_inventory_id_customer_id
ON ONLY public.rental USING BTREE (rental_date pg_catalog.timestamp_ops ASC NULLS LAST, inventory_id pg_catalog.int4_ops ASC NULLS LAST, customer_id pg_catalog.int2_ops ASC NULLS LAST) ;
CREATE UNIQUE INDEX IF NOT EXISTS rental_pkey
ON ONLY public.rental USING BTREE (rental_id pg_catalog.int4_ops ASC NULLS LAST) ;| Property | Value |
| Table | staff |
| Technical name | |
| Activated | true |
| Id | |
| Schema | public |
| Additional properties | true |
| $ref | |
| $ref | |
| Comments | |
| Temporary | false |
| Unlogged | false |
| If not exists | true |
| Inherits parent tables | |
| [1] Table name | |
| Partition of | |
| Partition bounds | |
| Partitioning | |
| [1] Partition method | |
| Partition by | |
| Partition key | |
| Expression | |
| Using method | |
| Storage parameters | |
| [1] Fill factor | |
| Parallel workers | |
| Enable autovacuum | |
| Enable TOAST autovacuum | |
| User catalog table | |
| Tablespace | pg_default |
| As Select statement | |
| Remarks | |
| Column | Type | Req | Key | Description | Comments |
| staff_id | integer | true | pk, dk | ||
| first_name | varchar(45) | true | |||
| last_name | varchar(45) | true | |||
| address_id | smallint | true | fk | ||
| varchar(50) | false | ||||
| store_id | smallint | true | |||
| active | boolean | true | |||
| username | varchar(16) | true | |||
| password | varchar(40) | false | |||
| last_update | timestamp | true | |||
| picture | bytea | false |
| Property | Value |
| Business Name | staff_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | integer |
| Array type | |
| Not null | true |
| Default | nextval('staff_staff_id_seq'::regclass) |
| Comments | |
| Primary key | true |
| Primary key options | |
| [1] Constraint name | staff_pkey |
| Include non-key columns | |
| With storage parameters | |
| Tablespace | |
| Comment | |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | first_name |
| Technical name | |
| Activated | true |
| Id | |
| Type | char |
| Subtype | varchar |
| Length | 45 |
| Array type | |
| Collation rule | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Min length | |
| Max length | |
| Pattern | |
| Format | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | last_name |
| Technical name | |
| Activated | true |
| Id | |
| Type | char |
| Subtype | varchar |
| Length | 45 |
| Array type | |
| Collation rule | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Min length | |
| Max length | |
| Pattern | |
| Format | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | address_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | smallint |
| Array type | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | address |
| Foreign field | address_id |
| Relationship type | Foreign Key |
| Relationship name | staff_address_id_fkey |
| Cardinality | 0..n |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | |
| Technical name | |
| Activated | true |
| Id | |
| Type | char |
| Subtype | varchar |
| Length | 50 |
| Array type | |
| Collation rule | |
| Not null | false |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Min length | |
| Max length | |
| Pattern | |
| Format | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | store_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | smallint |
| Array type | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | active |
| Technical name | |
| Activated | true |
| Id | |
| Type | boolean |
| Comments | |
| Array type | |
| Not null | true |
| Default | true |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | username |
| Technical name | |
| Activated | true |
| Id | |
| Type | char |
| Subtype | varchar |
| Length | 16 |
| Array type | |
| Collation rule | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Min length | |
| Max length | |
| Pattern | |
| Format | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | password |
| Technical name | |
| Activated | true |
| Id | |
| Type | char |
| Subtype | varchar |
| Length | 40 |
| Array type | |
| Collation rule | |
| Not null | false |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Min length | |
| Max length | |
| Pattern | |
| Format | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | last_update |
| Technical name | |
| Activated | true |
| Id | |
| Type | datetime |
| Subtype | timestamp |
| Precision | |
| Timezone | WITHOUT TIME ZONE |
| Comments | |
| Array type | |
| Not null | true |
| Default | now() |
| Primary key | false |
| Unique | false |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | picture |
| Technical name | |
| Activated | true |
| Id | |
| Type | binary |
| Subtype | bytea |
| Comments | |
| Array type | |
| Not null | false |
| Primary key | false |
| Unique | false |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Primary key | |
| [1] Constraint name | |
| Key | |
| Include non-key columns | |
| With storage parameters | |
| Index tablespace | |
| Comment | |
| Unique key | |
| [1] Constraint name | |
| Key | |
| Include non-key columns | |
| With storage parameters | |
| Index tablespace | |
| Comment | |
| Nulls Distinct | |
| Property | |
| Name | staff_pkey |
| Activated | true |
| Method | btree |
| Unique | true |
| Nulls Distinct | |
| Concurrent build | |
| If not exist | true |
| Only | true |
| Columns | |
| staff_id | |
| Include non-key columns | |
| With storage parameters | |
| [1] Fill factor | |
| Deduplicate items | |
| Tablespace | |
| Where constraint | |
| Comment | |
| Property | last_updated |
| Name | last_updated |
| Description | |
| Or replace | |
| Constraint | |
| Trigger type | BEFORE |
| Trigger events | |
| [1] Event | UPDATE |
| Update columns | |
| Referencing | |
| Trigger for each row/statement | FOR EACH ROW |
| Trigger WHEN condition | |
| Function | last_updated() |
| Remarks | |
{
"$schema": "http://json-schema.org/draft-04/schema#",
"type": "object",
"title": "staff",
"properties": {
"staff_id": {
"type": "number",
"default": "nextval('staff_staff_id_seq'::regclass)"
},
"first_name": {
"type": "string"
},
"last_name": {
"type": "string"
},
"address_id": {
"type": "number"
},
"email": {
"type": "string"
},
"store_id": {
"type": "number"
},
"active": {
"type": "boolean",
"default": true
},
"username": {
"type": "string"
},
"password": {
"type": "string"
},
"last_update": {
"type": "string",
"default": "now()"
},
"picture": {
"type": "string"
}
},
"additionalProperties": true,
"required": [
"staff_id",
"first_name",
"last_name",
"address_id",
"store_id",
"active",
"username",
"last_update"
]
}{
"staff_id": "nextval('staff_staff_id_seq'::regclass)",
"first_name": "Lorem",
"last_name": "Lorem",
"address_id": 56,
"email": "Lorem",
"store_id": -82,
"active": true,
"username": "Lorem",
"password": "Lorem",
"last_update": "now()"
}CREATE DATABASE dvdrental
ENCODING UTF8
LC_COLLATE 'en_US.UTF-8'
LC_CTYPE 'en_US.UTF-8'
TABLESPACE 'pg_default';
CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;
/*
CREATE TYPE public.mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');
*/
/*
CREATE DOMAIN public.year AS integer
CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));
*/
CREATE TABLE IF NOT EXISTS public.staff (
staff_id integer DEFAULT nextval('staff_staff_id_seq'::regclass) NOT NULL,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
address_id smallint NOT NULL,
email varchar(50),
store_id smallint NOT NULL,
active boolean DEFAULT true NOT NULL,
username varchar(16) NOT NULL,
password varchar(40),
last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
picture bytea,
CONSTRAINT staff_pkey PRIMARY KEY (staff_id),
CONSTRAINT staff_address_id_fkey FOREIGN KEY (address_id) REFERENCES public.address (address_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE
) TABLESPACE pg_default;
CREATE TRIGGER last_updated BEFORE UPDATE
ON public.staff
FOR EACH ROW
EXECUTE FUNCTION last_updated();
CREATE UNIQUE INDEX IF NOT EXISTS staff_pkey
ON ONLY public.staff USING BTREE (staff_id pg_catalog.int4_ops ASC NULLS LAST) ;| Property | Value |
| Table | store |
| Technical name | |
| Activated | true |
| Id | |
| Schema | public |
| Additional properties | true |
| $ref | |
| $ref | |
| Comments | |
| Temporary | false |
| Unlogged | false |
| If not exists | true |
| Inherits parent tables | |
| [1] Table name | |
| Partition of | |
| Partition bounds | |
| Partitioning | |
| [1] Partition method | |
| Partition by | |
| Partition key | |
| Expression | |
| Using method | |
| Storage parameters | |
| [1] Fill factor | |
| Parallel workers | |
| Enable autovacuum | |
| Autovacuum params | |
| Enable TOAST autovacuum | |
| TOAST parameters | |
| User catalog table | |
| Tablespace | pg_default |
| As Select statement | |
| Remarks | |
| Column | Type | Req | Key | Description | Comments |
| store_id | integer | true | pk | ||
| manager_staff_id | smallint | true | fk | ||
| address_id | smallint | true | fk | ||
| last_update | timestamp | true |
| Property | Value |
| Business Name | store_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | integer |
| Array type | |
| Not null | true |
| Default | nextval('store_store_id_seq'::regclass) |
| Comments | |
| Primary key | true |
| Primary key options | |
| [1] Constraint name | store_pkey |
| Include non-key columns | |
| With storage parameters | |
| Tablespace | |
| Comment | |
| Unique | false |
| Foreign table | |
| Foreign field | |
| Relationship type | |
| Relationship name | |
| Cardinality | |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | manager_staff_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | smallint |
| Array type | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | staff |
| Foreign field | staff_id |
| Relationship type | Foreign Key |
| Relationship name | store_manager_staff_id_fkey |
| Cardinality | 0..n |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | address_id |
| Technical name | |
| Activated | true |
| Id | |
| Type | numeric |
| Subtype | smallint |
| Array type | |
| Not null | true |
| Default | |
| Comments | |
| Primary key | false |
| Unique | false |
| Foreign table | address |
| Foreign field | address_id |
| Relationship type | Foreign Key |
| Relationship name | store_address_id_fkey |
| Cardinality | 0..n |
| Unit | |
| Min value | |
| Excl min | false |
| Max value | |
| Excl max | false |
| Multiple of | |
| Divisible by | |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Business Name | last_update |
| Technical name | |
| Activated | true |
| Id | |
| Type | datetime |
| Subtype | timestamp |
| Precision | |
| Timezone | WITHOUT TIME ZONE |
| Comments | |
| Array type | |
| Not null | true |
| Default | now() |
| Primary key | false |
| Unique | false |
| Pattern | |
| Enum | |
| Faker function | |
| Sample | |
| Remarks | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | [object Object] |
| Property | Value |
| Primary key | |
| [1] Constraint name | |
| Key | |
| Include non-key columns | |
| With storage parameters | |
| Index tablespace | |
| Comment | |
| Unique key | |
| [1] Constraint name | |
| Key | |
| Include non-key columns | |
| With storage parameters | |
| Index tablespace | |
| Comment | |
| Nulls Distinct | |
| Property | |
| Name | idx_unq_manager_staff_id |
| Activated | true |
| Method | btree |
| Unique | true |
| Nulls Distinct | |
| Concurrent build | |
| If not exist | true |
| Only | true |
| Columns | |
| manager_staff_id | |
| Include non-key columns | |
| With storage parameters | |
| [1] Fill factor | |
| Deduplicate items | |
| Tablespace | |
| Where constraint | |
| Comment | |
| Property | |
| Name | store_pkey |
| Activated | true |
| Method | btree |
| Unique | true |
| Nulls Distinct | |
| Concurrent build | |
| If not exist | true |
| Only | true |
| Columns | |
| store_id | |
| Include non-key columns | |
| With storage parameters | |
| [1] Fill factor | |
| Deduplicate items | |
| Tablespace | |
| Where constraint | |
| Comment | |
| Property | last_updated |
| Name | last_updated |
| Description | |
| Or replace | |
| Constraint | |
| Trigger type | BEFORE |
| Trigger events | |
| [1] Event | UPDATE |
| Update columns | |
| Referencing | |
| Trigger for each row/statement | FOR EACH ROW |
| Trigger WHEN condition | |
| Function | last_updated() |
| Remarks | |
{
"$schema": "http://json-schema.org/draft-04/schema#",
"type": "object",
"title": "store",
"properties": {
"store_id": {
"type": "number",
"default": "nextval('store_store_id_seq'::regclass)"
},
"manager_staff_id": {
"type": "number"
},
"address_id": {
"type": "number"
},
"last_update": {
"type": "string",
"default": "now()"
}
},
"additionalProperties": true,
"required": [
"store_id",
"manager_staff_id",
"address_id",
"last_update"
]
}{
"store_id": "nextval('store_store_id_seq'::regclass)",
"manager_staff_id": -96,
"address_id": -68,
"last_update": "now()"
}CREATE DATABASE dvdrental
ENCODING UTF8
LC_COLLATE 'en_US.UTF-8'
LC_CTYPE 'en_US.UTF-8'
TABLESPACE 'pg_default';
CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;
/*
CREATE TYPE public.mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');
*/
/*
CREATE DOMAIN public.year AS integer
CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));
*/
CREATE TABLE IF NOT EXISTS public.store (
store_id integer DEFAULT nextval('store_store_id_seq'::regclass) NOT NULL,
manager_staff_id smallint NOT NULL,
address_id smallint NOT NULL,
last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
CONSTRAINT store_pkey PRIMARY KEY (store_id),
CONSTRAINT store_address_id_fkey FOREIGN KEY (address_id) REFERENCES public.address (address_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT store_manager_staff_id_fkey FOREIGN KEY (manager_staff_id) REFERENCES public.staff (staff_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE
) TABLESPACE pg_default;
CREATE TRIGGER last_updated BEFORE UPDATE
ON public.store
FOR EACH ROW
EXECUTE FUNCTION last_updated();
CREATE UNIQUE INDEX IF NOT EXISTS idx_unq_manager_staff_id
ON ONLY public.store USING BTREE (manager_staff_id pg_catalog.int2_ops ASC NULLS LAST) ;
CREATE UNIQUE INDEX IF NOT EXISTS store_pkey
ON ONLY public.store USING BTREE (store_id pg_catalog.int4_ops ASC NULLS LAST) ;| Property | _group_concat |
| Name | _group_concat |
| Comments | |
| Or replace | |
| Arguments | |
| [1] Arg mode | IN |
| Argument name | |
| Argument type | text |
| Default expression | |
| [2] Arg mode | IN |
| Argument name | |
| Argument type | text |
| Default expression | |
| Returns set of | |
| Returns data type | text |
| Language | sql |
| Definition | SELECT CASE WHEN $2 IS NULL THEN $1 WHEN $1 IS NULL THEN $2 ELSE $1 || ', ' || $2 END |
| Volatility | IMMUTABLE |
| Leak proof | |
| When NULL args | CALLED ON NULL INPUT |
| SQL Security | INVOKER |
| Parallel | |
| Estimated cost | |
| Support function | |
| Config parameters | |
| Remarks | |
| Property | film_in_stock |
| Name | film_in_stock |
| Comments | |
| Or replace | |
| Arguments | |
| [1] Arg mode | IN |
| Argument name | p_film_id |
| Argument type | integer |
| Default expression | |
| [2] Arg mode | IN |
| Argument name | p_store_id |
| Argument type | integer |
| Default expression | |
| [3] Arg mode | OUT |
| Argument name | p_film_count |
| Argument type | integer |
| Default expression | |
| Returns set of | true |
| Returns data type | int4 |
| Language | sql |
| Definition | SELECT inventory_id FROM inventory WHERE film_id = $1 AND store_id = $2 AND inventory_in_stock(inventory_id); |
| Volatility | VOLATILE |
| Leak proof | |
| When NULL args | CALLED ON NULL INPUT |
| SQL Security | INVOKER |
| Parallel | |
| Estimated cost | |
| Estimated rows | |
| Support function | |
| Config parameters | |
| Remarks | |
| Property | film_not_in_stock |
| Name | film_not_in_stock |
| Comments | |
| Or replace | |
| Arguments | |
| [1] Arg mode | IN |
| Argument name | p_film_id |
| Argument type | integer |
| Default expression | |
| [2] Arg mode | IN |
| Argument name | p_store_id |
| Argument type | integer |
| Default expression | |
| [3] Arg mode | OUT |
| Argument name | p_film_count |
| Argument type | integer |
| Default expression | |
| Returns set of | true |
| Returns data type | int4 |
| Language | sql |
| Definition | SELECT inventory_id FROM inventory WHERE film_id = $1 AND store_id = $2 AND NOT inventory_in_stock(inventory_id); |
| Volatility | VOLATILE |
| Leak proof | |
| When NULL args | CALLED ON NULL INPUT |
| SQL Security | INVOKER |
| Parallel | |
| Estimated cost | |
| Estimated rows | |
| Support function | |
| Config parameters | |
| Remarks | |
| Property | get_customer_balance |
| Name | get_customer_balance |
| Comments | |
| Or replace | |
| Arguments | |
| [1] Arg mode | IN |
| Argument name | p_customer_id |
| Argument type | integer |
| Default expression | |
| [2] Arg mode | IN |
| Argument name | p_effective_date |
| Argument type | timestamp without time zone |
| Default expression | |
| Returns set of | |
| Returns data type | numeric |
| Language | plpgsql |
| Definition | --#OK, WE NEED TO CALCULATE THE CURRENT BALANCE GIVEN A CUSTOMER_ID AND A DATE --#THAT WE WANT THE BALANCE TO BE EFFECTIVE FOR. THE BALANCE IS: --# 1) RENTAL FEES FOR ALL PREVIOUS RENTALS --# 2) ONE DOLLAR FOR EVERY DAY THE PREVIOUS RENTALS ARE OVERDUE --# 3) IF A FILM IS MORE THAN RENTAL_DURATION * 2 OVERDUE, CHARGE THE REPLACEMENT_COST --# 4) SUBTRACT ALL PAYMENTS MADE BEFORE THE DATE SPECIFIED DECLARE v_rentfees DECIMAL(5,2); --#FEES PAID TO RENT THE VIDEOS INITIALLY v_overfees INTEGER; --#LATE FEES FOR PRIOR RENTALS v_payments DECIMAL(5,2); --#SUM OF PAYMENTS MADE PREVIOUSLY BEGIN SELECT COALESCE(SUM(film.rental_rate),0) INTO v_rentfees FROM film, inventory, rental WHERE film.film_id = inventory.film_id AND inventory.inventory_id = rental.inventory_id AND rental.rental_date <= p_effective_date AND rental.customer_id = p_customer_id; SELECT COALESCE(SUM(IF((rental.return_date - rental.rental_date) > (film.rental_duration * '1 day'::interval), ((rental.return_date - rental.rental_date) - (film.rental_duration * '1 day'::interval)),0)),0) INTO v_overfees FROM rental, inventory, film WHERE film.film_id = inventory.film_id AND inventory.inventory_id = rental.inventory_id AND rental.rental_date <= p_effective_date AND rental.customer_id = p_customer_id; SELECT COALESCE(SUM(payment.amount),0) INTO v_payments FROM payment WHERE payment.payment_date <= p_effective_date AND payment.customer_id = p_customer_id; RETURN v_rentfees + v_overfees - v_payments; END |
| Volatility | VOLATILE |
| Leak proof | |
| When NULL args | CALLED ON NULL INPUT |
| SQL Security | INVOKER |
| Parallel | |
| Estimated cost | |
| Support function | |
| Config parameters | |
| Remarks | |
| Property | inventory_held_by_customer |
| Name | inventory_held_by_customer |
| Comments | |
| Or replace | |
| Arguments | |
| [1] Arg mode | IN |
| Argument name | p_inventory_id |
| Argument type | integer |
| Default expression | |
| Returns set of | |
| Returns data type | int4 |
| Language | plpgsql |
| Definition | DECLARE v_customer_id INTEGER; BEGIN SELECT customer_id INTO v_customer_id FROM rental WHERE return_date IS NULL AND inventory_id = p_inventory_id; RETURN v_customer_id; END |
| Volatility | VOLATILE |
| Leak proof | |
| When NULL args | CALLED ON NULL INPUT |
| SQL Security | INVOKER |
| Parallel | |
| Estimated cost | |
| Support function | |
| Config parameters | |
| Remarks | |
| Property | inventory_in_stock |
| Name | inventory_in_stock |
| Comments | |
| Or replace | |
| Arguments | |
| [1] Arg mode | IN |
| Argument name | p_inventory_id |
| Argument type | integer |
| Default expression | |
| Returns set of | |
| Returns data type | bool |
| Language | plpgsql |
| Definition | DECLARE v_rentals INTEGER; v_out INTEGER; BEGIN -- AN ITEM IS IN-STOCK IF THERE ARE EITHER NO ROWS IN THE rental TABLE -- FOR THE ITEM OR ALL ROWS HAVE return_date POPULATED SELECT count(*) INTO v_rentals FROM rental WHERE inventory_id = p_inventory_id; IF v_rentals = 0 THEN RETURN TRUE; END IF; SELECT COUNT(rental_id) INTO v_out FROM inventory LEFT JOIN rental USING(inventory_id) WHERE inventory.inventory_id = p_inventory_id AND rental.return_date IS NULL; IF v_out > 0 THEN RETURN FALSE; ELSE RETURN TRUE; END IF; END |
| Volatility | VOLATILE |
| Leak proof | |
| When NULL args | CALLED ON NULL INPUT |
| SQL Security | INVOKER |
| Parallel | |
| Estimated cost | |
| Support function | |
| Config parameters | |
| Remarks | |
| Property | last_day |
| Name | last_day |
| Comments | |
| Or replace | |
| Arguments | |
| [1] Arg mode | IN |
| Argument name | |
| Argument type | timestamp without time zone |
| Default expression | |
| Returns set of | |
| Returns data type | date |
| Language | sql |
| Definition | SELECT CASE WHEN EXTRACT(MONTH FROM $1) = 12 THEN (((EXTRACT(YEAR FROM $1) + 1) operator(pg_catalog.||) '-01-01')::date - INTERVAL '1 day')::date ELSE ((EXTRACT(YEAR FROM $1) operator(pg_catalog.||) '-' operator(pg_catalog.||) (EXTRACT(MONTH FROM $1) + 1) operator(pg_catalog.||) '-01')::date - INTERVAL '1 day')::date END |
| Volatility | IMMUTABLE |
| Leak proof | |
| When NULL args | STRICT |
| SQL Security | INVOKER |
| Parallel | |
| Estimated cost | |
| Support function | |
| Config parameters | |
| Remarks | |
| Property | last_updated |
| Name | last_updated |
| Comments | |
| Or replace | |
| Arguments | |
| [1] Arg mode | |
| Argument name | |
| Argument type | |
| Default expression | |
| Returns set of | |
| Returns data type | trigger |
| Language | plpgsql |
| Definition | BEGIN NEW.last_update = CURRENT_TIMESTAMP; RETURN NEW; END |
| Volatility | VOLATILE |
| Leak proof | |
| When NULL args | CALLED ON NULL INPUT |
| SQL Security | INVOKER |
| Parallel | |
| Estimated cost | |
| Support function | |
| Config parameters | |
| Remarks | |
| Property | rewards_report |
| Name | rewards_report |
| Comments | |
| Or replace | |
| Arguments | |
| [1] Arg mode | IN |
| Argument name | min_monthly_purchases |
| Argument type | integer |
| Default expression | |
| [2] Arg mode | IN |
| Argument name | min_dollar_amount_purchased |
| Argument type | numeric |
| Default expression | |
| Returns set of | true |
| Returns data type | customer |
| Language | plpgsql |
| Definition | DECLARE last_month_start DATE; last_month_end DATE; rr RECORD; tmpSQL TEXT; BEGIN /* Some sanity checks... */ IF min_monthly_purchases = 0 THEN RAISE EXCEPTION 'Minimum monthly purchases parameter must be > 0'; END IF; IF min_dollar_amount_purchased = 0.00 THEN RAISE EXCEPTION 'Minimum monthly dollar amount purchased parameter must be > $0.00'; END IF; last_month_start := CURRENT_DATE - '3 month'::interval; last_month_start := to_date((extract(YEAR FROM last_month_start) || '-' || extract(MONTH FROM last_month_start) || '-01'),'YYYY-MM-DD'); last_month_end := LAST_DAY(last_month_start); /* Create a temporary storage area for Customer IDs. */ CREATE TEMPORARY TABLE tmpCustomer (customer_id INTEGER NOT NULL PRIMARY KEY); /* Find all customers meeting the monthly purchase requirements */ tmpSQL := 'INSERT INTO tmpCustomer (customer_id) SELECT p.customer_id FROM payment AS p WHERE DATE(p.payment_date) BETWEEN '||quote_literal(last_month_start) ||' AND '|| quote_literal(last_month_end) || ' GROUP BY customer_id HAVING SUM(p.amount) > '|| min_dollar_amount_purchased || ' AND COUNT(customer_id) > ' ||min_monthly_purchases ; EXECUTE tmpSQL; /* Output ALL customer information of matching rewardees. Customize output as needed. */ FOR rr IN EXECUTE 'SELECT c.* FROM tmpCustomer AS t INNER JOIN customer AS c ON t.customer_id = c.customer_id' LOOP RETURN NEXT rr; END LOOP; /* Clean up */ tmpSQL := 'DROP TABLE tmpCustomer'; EXECUTE tmpSQL; RETURN; END |
| Volatility | VOLATILE |
| Leak proof | |
| When NULL args | CALLED ON NULL INPUT |
| SQL Security | DEFINER |
| Parallel | |
| Estimated cost | |
| Estimated rows | |
| Support function | |
| Config parameters | |
| Remarks | |
CREATE DATABASE dvdrental
ENCODING UTF8
LC_COLLATE 'en_US.UTF-8'
LC_CTYPE 'en_US.UTF-8'
TABLESPACE 'pg_default';
CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;
CREATE FUNCTION public._group_concat
(IN text, IN text)
RETURNS text
LANGUAGE sql
IMMUTABLE
NOT LEAKPROOF
CALLED ON NULL INPUT
SECURITY INVOKER
AS $BODY$
SELECT CASE
WHEN $2 IS NULL THEN $1
WHEN $1 IS NULL THEN $2
ELSE $1 || ', ' || $2
END
$BODY$;
CREATE FUNCTION public.film_in_stock
(IN p_film_id integer, IN p_store_id integer, OUT p_film_count integer)
RETURNS SETOF int4
LANGUAGE sql
VOLATILE
NOT LEAKPROOF
CALLED ON NULL INPUT
SECURITY INVOKER
AS $BODY$
SELECT inventory_id
FROM inventory
WHERE film_id = $1
AND store_id = $2
AND inventory_in_stock(inventory_id);
$BODY$;
CREATE FUNCTION public.film_not_in_stock
(IN p_film_id integer, IN p_store_id integer, OUT p_film_count integer)
RETURNS SETOF int4
LANGUAGE sql
VOLATILE
NOT LEAKPROOF
CALLED ON NULL INPUT
SECURITY INVOKER
AS $BODY$
SELECT inventory_id
FROM inventory
WHERE film_id = $1
AND store_id = $2
AND NOT inventory_in_stock(inventory_id);
$BODY$;
CREATE FUNCTION public.get_customer_balance
(IN p_customer_id integer, IN p_effective_date timestamp without time zone)
RETURNS numeric
LANGUAGE plpgsql
VOLATILE
NOT LEAKPROOF
CALLED ON NULL INPUT
SECURITY INVOKER
AS $BODY$
--#OK, WE NEED TO CALCULATE THE CURRENT BALANCE GIVEN A CUSTOMER_ID AND A DATE
--#THAT WE WANT THE BALANCE TO BE EFFECTIVE FOR. THE BALANCE IS:
--# 1) RENTAL FEES FOR ALL PREVIOUS RENTALS
--# 2) ONE DOLLAR FOR EVERY DAY THE PREVIOUS RENTALS ARE OVERDUE
--# 3) IF A FILM IS MORE THAN RENTAL_DURATION * 2 OVERDUE, CHARGE THE REPLACEMENT_COST
--# 4) SUBTRACT ALL PAYMENTS MADE BEFORE THE DATE SPECIFIED
DECLARE
v_rentfees DECIMAL(5,2); --#FEES PAID TO RENT THE VIDEOS INITIALLY
v_overfees INTEGER; --#LATE FEES FOR PRIOR RENTALS
v_payments DECIMAL(5,2); --#SUM OF PAYMENTS MADE PREVIOUSLY
BEGIN
SELECT COALESCE(SUM(film.rental_rate),0) INTO v_rentfees
FROM film, inventory, rental
WHERE film.film_id = inventory.film_id
AND inventory.inventory_id = rental.inventory_id
AND rental.rental_date <= p_effective_date
AND rental.customer_id = p_customer_id;
SELECT COALESCE(SUM(IF((rental.return_date - rental.rental_date) > (film.rental_duration * '1 day'::interval),
((rental.return_date - rental.rental_date) - (film.rental_duration * '1 day'::interval)),0)),0) INTO v_overfees
FROM rental, inventory, film
WHERE film.film_id = inventory.film_id
AND inventory.inventory_id = rental.inventory_id
AND rental.rental_date <= p_effective_date
AND rental.customer_id = p_customer_id;
SELECT COALESCE(SUM(payment.amount),0) INTO v_payments
FROM payment
WHERE payment.payment_date <= p_effective_date
AND payment.customer_id = p_customer_id;
RETURN v_rentfees + v_overfees - v_payments;
END
$BODY$;
CREATE FUNCTION public.inventory_held_by_customer
(IN p_inventory_id integer)
RETURNS int4
LANGUAGE plpgsql
VOLATILE
NOT LEAKPROOF
CALLED ON NULL INPUT
SECURITY INVOKER
AS $BODY$
DECLARE
v_customer_id INTEGER;
BEGIN
SELECT customer_id INTO v_customer_id
FROM rental
WHERE return_date IS NULL
AND inventory_id = p_inventory_id;
RETURN v_customer_id;
END
$BODY$;
CREATE FUNCTION public.inventory_in_stock
(IN p_inventory_id integer)
RETURNS bool
LANGUAGE plpgsql
VOLATILE
NOT LEAKPROOF
CALLED ON NULL INPUT
SECURITY INVOKER
AS $BODY$
DECLARE
v_rentals INTEGER;
v_out INTEGER;
BEGIN
-- AN ITEM IS IN-STOCK IF THERE ARE EITHER NO ROWS IN THE rental TABLE
-- FOR THE ITEM OR ALL ROWS HAVE return_date POPULATED
SELECT count(*) INTO v_rentals
FROM rental
WHERE inventory_id = p_inventory_id;
IF v_rentals = 0 THEN
RETURN TRUE;
END IF;
SELECT COUNT(rental_id) INTO v_out
FROM inventory LEFT JOIN rental USING(inventory_id)
WHERE inventory.inventory_id = p_inventory_id
AND rental.return_date IS NULL;
IF v_out > 0 THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END
$BODY$;
CREATE FUNCTION public.last_day
(IN timestamp without time zone)
RETURNS date
LANGUAGE sql
IMMUTABLE
NOT LEAKPROOF
STRICT
SECURITY INVOKER
AS $BODY$
SELECT CASE
WHEN EXTRACT(MONTH FROM $1) = 12 THEN
(((EXTRACT(YEAR FROM $1) + 1) operator(pg_catalog.||) '-01-01')::date - INTERVAL '1 day')::date
ELSE
((EXTRACT(YEAR FROM $1) operator(pg_catalog.||) '-' operator(pg_catalog.||) (EXTRACT(MONTH FROM $1) + 1) operator(pg_catalog.||) '-01')::date - INTERVAL '1 day')::date
END
$BODY$;
CREATE FUNCTION public.last_updated
()
RETURNS trigger
LANGUAGE plpgsql
VOLATILE
NOT LEAKPROOF
CALLED ON NULL INPUT
SECURITY INVOKER
AS $BODY$
BEGIN
NEW.last_update = CURRENT_TIMESTAMP;
RETURN NEW;
END
$BODY$;
CREATE FUNCTION public.rewards_report
(IN min_monthly_purchases integer, IN min_dollar_amount_purchased numeric)
RETURNS SETOF customer
LANGUAGE plpgsql
VOLATILE
NOT LEAKPROOF
CALLED ON NULL INPUT
SECURITY DEFINER
AS $BODY$
DECLARE
last_month_start DATE;
last_month_end DATE;
rr RECORD;
tmpSQL TEXT;
BEGIN
/* Some sanity checks... */
IF min_monthly_purchases = 0 THEN
RAISE EXCEPTION 'Minimum monthly purchases parameter must be > 0';
END IF;
IF min_dollar_amount_purchased = 0.00 THEN
RAISE EXCEPTION 'Minimum monthly dollar amount purchased parameter must be > $0.00';
END IF;
last_month_start := CURRENT_DATE - '3 month'::interval;
last_month_start := to_date((extract(YEAR FROM last_month_start) || '-' || extract(MONTH FROM last_month_start) || '-01'),'YYYY-MM-DD');
last_month_end := LAST_DAY(last_month_start);
/*
Create a temporary storage area for Customer IDs.
*/
CREATE TEMPORARY TABLE tmpCustomer (customer_id INTEGER NOT NULL PRIMARY KEY);
/*
Find all customers meeting the monthly purchase requirements
*/
tmpSQL := 'INSERT INTO tmpCustomer (customer_id)
SELECT p.customer_id
FROM payment AS p
WHERE DATE(p.payment_date) BETWEEN '||quote_literal(last_month_start) ||' AND '|| quote_literal(last_month_end) || '
GROUP BY customer_id
HAVING SUM(p.amount) > '|| min_dollar_amount_purchased || '
AND COUNT(customer_id) > ' ||min_monthly_purchases ;
EXECUTE tmpSQL;
/*
Output ALL customer information of matching rewardees.
Customize output as needed.
*/
FOR rr IN EXECUTE 'SELECT c.* FROM tmpCustomer AS t INNER JOIN customer AS c ON t.customer_id = c.customer_id' LOOP
RETURN NEXT rr;
END LOOP;
/* Clean up */
tmpSQL := 'DROP TABLE tmpCustomer';
EXECUTE tmpSQL;
RETURN;
END
$BODY$;
CREATE TYPE public.mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');
CREATE DOMAIN public.year AS integer
CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));
CREATE TABLE IF NOT EXISTS public.actor (
actor_id integer DEFAULT nextval('actor_actor_id_seq'::regclass) NOT NULL,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
CONSTRAINT actor_pkey PRIMARY KEY (actor_id)
) TABLESPACE pg_default;
CREATE TRIGGER last_updated BEFORE UPDATE
ON public.actor
FOR EACH ROW
EXECUTE FUNCTION last_updated();
CREATE UNIQUE INDEX IF NOT EXISTS actor_pkey
ON ONLY public.actor USING BTREE (actor_id pg_catalog.int4_ops ASC NULLS LAST) ;
CREATE INDEX IF NOT EXISTS idx_actor_last_name
ON ONLY public.actor USING BTREE (last_name COLLATE pg_catalog."default" pg_catalog.text_ops ASC NULLS LAST) ;
CREATE TABLE IF NOT EXISTS public.country (
country_id integer DEFAULT nextval('country_country_id_seq'::regclass) NOT NULL,
country varchar(50) NOT NULL,
last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
CONSTRAINT country_pkey PRIMARY KEY (country_id)
) TABLESPACE pg_default;
CREATE TRIGGER last_updated BEFORE UPDATE
ON public.country
FOR EACH ROW
EXECUTE FUNCTION last_updated();
CREATE UNIQUE INDEX IF NOT EXISTS country_pkey
ON ONLY public.country USING BTREE (country_id pg_catalog.int4_ops ASC NULLS LAST) ;
CREATE TABLE IF NOT EXISTS public.city (
city_id integer DEFAULT nextval('city_city_id_seq'::regclass) NOT NULL,
city varchar(50) NOT NULL,
country_id smallint NOT NULL,
last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
CONSTRAINT city_pkey PRIMARY KEY (city_id),
CONSTRAINT fk_city FOREIGN KEY (country_id) REFERENCES public.country (country_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
) TABLESPACE pg_default;
CREATE TRIGGER last_updated BEFORE UPDATE
ON public.city
FOR EACH ROW
EXECUTE FUNCTION last_updated();
CREATE UNIQUE INDEX IF NOT EXISTS city_pkey
ON ONLY public.city USING BTREE (city_id pg_catalog.int4_ops ASC NULLS LAST) ;
CREATE INDEX IF NOT EXISTS idx_fk_country_id
ON ONLY public.city USING BTREE (country_id pg_catalog.int2_ops ASC NULLS LAST) ;
CREATE TABLE IF NOT EXISTS public.address (
address_id integer DEFAULT nextval('address_address_id_seq'::regclass) NOT NULL,
address varchar(50) NOT NULL,
address2 varchar(50),
district varchar(20) NOT NULL,
city_id smallint NOT NULL,
postal_code varchar(10),
phone varchar(20) NOT NULL,
last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
CONSTRAINT address_pkey PRIMARY KEY (address_id),
CONSTRAINT fk_address_city FOREIGN KEY (city_id) REFERENCES public.city (city_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
) TABLESPACE pg_default;
CREATE TRIGGER last_updated BEFORE UPDATE
ON public.address
FOR EACH ROW
EXECUTE FUNCTION last_updated();
CREATE UNIQUE INDEX IF NOT EXISTS address_pkey
ON ONLY public.address USING BTREE (address_id pg_catalog.int4_ops ASC NULLS LAST) ;
CREATE INDEX IF NOT EXISTS idx_fk_city_id
ON ONLY public.address USING BTREE (city_id pg_catalog.int2_ops ASC NULLS LAST) ;
CREATE TABLE IF NOT EXISTS public.category (
category_id integer DEFAULT nextval('category_category_id_seq'::regclass) NOT NULL,
name varchar(25) NOT NULL,
last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
CONSTRAINT category_pkey PRIMARY KEY (category_id)
) TABLESPACE pg_default;
CREATE TRIGGER last_updated BEFORE UPDATE
ON public.category
FOR EACH ROW
EXECUTE FUNCTION last_updated();
CREATE UNIQUE INDEX IF NOT EXISTS category_pkey
ON ONLY public.category USING BTREE (category_id pg_catalog.int4_ops ASC NULLS LAST) ;
CREATE TABLE IF NOT EXISTS public.customer (
customer_id integer DEFAULT nextval('customer_customer_id_seq'::regclass) NOT NULL,
store_id smallint NOT NULL,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
email varchar(50),
address_id smallint NOT NULL,
activebool boolean DEFAULT true NOT NULL,
create_date date DEFAULT E'(\'now\'::text)::date' NOT NULL,
last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()',
active integer,
CONSTRAINT customer_pkey PRIMARY KEY (customer_id),
CONSTRAINT customer_address_id_fkey FOREIGN KEY (address_id) REFERENCES public.address (address_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE
) TABLESPACE pg_default;
CREATE TRIGGER last_updated BEFORE UPDATE
ON public.customer
FOR EACH ROW
EXECUTE FUNCTION last_updated();
CREATE UNIQUE INDEX IF NOT EXISTS customer_pkey
ON ONLY public.customer USING BTREE (customer_id pg_catalog.int4_ops ASC NULLS LAST) ;
CREATE INDEX IF NOT EXISTS idx_fk_address_id
ON ONLY public.customer USING BTREE (address_id pg_catalog.int2_ops ASC NULLS LAST) ;
CREATE INDEX IF NOT EXISTS idx_fk_store_id
ON ONLY public.customer USING BTREE (store_id pg_catalog.int2_ops ASC NULLS LAST) ;
CREATE INDEX IF NOT EXISTS idx_last_name
ON ONLY public.customer USING BTREE (last_name COLLATE pg_catalog."default" pg_catalog.text_ops ASC NULLS LAST) ;
CREATE TABLE IF NOT EXISTS public.language (
language_id integer DEFAULT nextval('language_language_id_seq'::regclass) NOT NULL,
name char(20) NOT NULL,
last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
CONSTRAINT language_pkey PRIMARY KEY (language_id)
) TABLESPACE pg_default;
CREATE TRIGGER last_updated BEFORE UPDATE
ON public.language
FOR EACH ROW
EXECUTE FUNCTION last_updated();
CREATE UNIQUE INDEX IF NOT EXISTS language_pkey
ON ONLY public.language USING BTREE (language_id pg_catalog.int4_ops ASC NULLS LAST) ;
CREATE TABLE IF NOT EXISTS public.film (
film_id integer DEFAULT nextval('film_film_id_seq'::regclass) NOT NULL,
title varchar(255) NOT NULL,
description text,
release_year year,
language_id smallint NOT NULL,
rental_duration smallint DEFAULT 3 NOT NULL,
rental_rate numeric(4,2) DEFAULT 4.99 NOT NULL,
length smallint,
replacement_cost numeric(5,2) DEFAULT 19.99 NOT NULL,
rating mpaa_rating,
last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
special_features text[],
fulltext tsvector NOT NULL,
zendesk varchar(10),
CONSTRAINT film_pkey PRIMARY KEY (film_id),
CONSTRAINT film_language_id_fkey FOREIGN KEY (language_id) REFERENCES public.language (language_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE
) TABLESPACE pg_default;
CREATE TRIGGER film_fulltext_trigger BEFORE INSERT OR UPDATE
ON public.film
FOR EACH ROW
EXECUTE FUNCTION tsvector_update_trigger('fulltext', 'pg_catalog.english', 'title', 'description');
CREATE TRIGGER last_updated BEFORE UPDATE
ON public.film
FOR EACH ROW
EXECUTE FUNCTION last_updated();
CREATE INDEX IF NOT EXISTS film_fulltext_idx
ON ONLY public.film USING GIST (fulltext pg_catalog.tsvector_ops) ;
CREATE UNIQUE INDEX IF NOT EXISTS film_pkey
ON ONLY public.film USING BTREE (film_id pg_catalog.int4_ops ASC NULLS LAST) ;
CREATE INDEX IF NOT EXISTS idx_fk_language_id
ON ONLY public.film USING BTREE (language_id pg_catalog.int2_ops ASC NULLS LAST) ;
CREATE INDEX IF NOT EXISTS idx_title
ON ONLY public.film USING BTREE (title COLLATE pg_catalog."default" pg_catalog.text_ops ASC NULLS LAST) ;
CREATE TABLE IF NOT EXISTS public.film_actor (
actor_id smallint NOT NULL,
film_id smallint NOT NULL,
last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
CONSTRAINT film_actor_pkey PRIMARY KEY (actor_id, film_id),
CONSTRAINT film_actor_actor_id_fkey FOREIGN KEY (actor_id) REFERENCES public.actor (actor_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT film_actor_film_id_fkey FOREIGN KEY (film_id) REFERENCES public.film (film_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE
) TABLESPACE pg_default;
CREATE TRIGGER last_updated BEFORE UPDATE
ON public.film_actor
FOR EACH ROW
EXECUTE FUNCTION last_updated();
CREATE UNIQUE INDEX IF NOT EXISTS film_actor_pkey
ON ONLY public.film_actor USING BTREE (actor_id pg_catalog.int2_ops ASC NULLS LAST, film_id pg_catalog.int2_ops ASC NULLS LAST) ;
CREATE INDEX IF NOT EXISTS idx_fk_film_id
ON ONLY public.film_actor USING BTREE (film_id pg_catalog.int2_ops ASC NULLS LAST) ;
CREATE TABLE IF NOT EXISTS public.film_category (
film_id smallint NOT NULL,
category_id smallint NOT NULL,
last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
CONSTRAINT film_category_pkey PRIMARY KEY (film_id, category_id),
CONSTRAINT film_category_category_id_fkey FOREIGN KEY (category_id) REFERENCES public.category (category_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT film_category_film_id_fkey FOREIGN KEY (film_id) REFERENCES public.film (film_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE
) TABLESPACE pg_default;
CREATE TRIGGER last_updated BEFORE UPDATE
ON public.film_category
FOR EACH ROW
EXECUTE FUNCTION last_updated();
CREATE UNIQUE INDEX IF NOT EXISTS film_category_pkey
ON ONLY public.film_category USING BTREE (film_id pg_catalog.int2_ops ASC NULLS LAST, category_id pg_catalog.int2_ops ASC NULLS LAST) ;
CREATE TABLE IF NOT EXISTS public.inventory (
inventory_id integer DEFAULT nextval('inventory_inventory_id_seq'::regclass) NOT NULL,
film_id smallint NOT NULL,
store_id smallint NOT NULL,
last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
CONSTRAINT inventory_pkey PRIMARY KEY (inventory_id),
CONSTRAINT inventory_film_id_fkey FOREIGN KEY (film_id) REFERENCES public.film (film_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE
) TABLESPACE pg_default;
CREATE TRIGGER last_updated BEFORE UPDATE
ON public.inventory
FOR EACH ROW
EXECUTE FUNCTION last_updated();
CREATE INDEX IF NOT EXISTS idx_store_id_film_id
ON ONLY public.inventory USING BTREE (store_id pg_catalog.int2_ops ASC NULLS LAST, film_id pg_catalog.int2_ops ASC NULLS LAST) ;
CREATE UNIQUE INDEX IF NOT EXISTS inventory_pkey
ON ONLY public.inventory USING BTREE (inventory_id pg_catalog.int4_ops ASC NULLS LAST) ;
CREATE TABLE IF NOT EXISTS public.staff (
staff_id integer DEFAULT nextval('staff_staff_id_seq'::regclass) NOT NULL,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
address_id smallint NOT NULL,
email varchar(50),
store_id smallint NOT NULL,
active boolean DEFAULT true NOT NULL,
username varchar(16) NOT NULL,
password varchar(40),
last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
picture bytea,
CONSTRAINT staff_pkey PRIMARY KEY (staff_id),
CONSTRAINT staff_address_id_fkey FOREIGN KEY (address_id) REFERENCES public.address (address_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE
) TABLESPACE pg_default;
CREATE TRIGGER last_updated BEFORE UPDATE
ON public.staff
FOR EACH ROW
EXECUTE FUNCTION last_updated();
CREATE UNIQUE INDEX IF NOT EXISTS staff_pkey
ON ONLY public.staff USING BTREE (staff_id pg_catalog.int4_ops ASC NULLS LAST) ;
CREATE TABLE IF NOT EXISTS public.rental (
rental_id integer DEFAULT nextval('rental_rental_id_seq'::regclass) NOT NULL,
rental_date timestamp WITHOUT TIME ZONE NOT NULL,
inventory_id integer NOT NULL,
customer_id smallint NOT NULL,
return_date timestamp WITHOUT TIME ZONE,
staff_id smallint NOT NULL,
last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
CONSTRAINT rental_pkey PRIMARY KEY (rental_id),
CONSTRAINT rental_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES public.customer (customer_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT rental_inventory_id_fkey FOREIGN KEY (inventory_id) REFERENCES public.inventory (inventory_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT rental_staff_id_key FOREIGN KEY (staff_id) REFERENCES public.staff (staff_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
) TABLESPACE pg_default;
CREATE TRIGGER last_updated BEFORE UPDATE
ON public.rental
FOR EACH ROW
EXECUTE FUNCTION last_updated();
CREATE INDEX IF NOT EXISTS idx_fk_inventory_id
ON ONLY public.rental USING BTREE (inventory_id pg_catalog.int4_ops ASC NULLS LAST) ;
CREATE UNIQUE INDEX IF NOT EXISTS idx_unq_rental_rental_date_inventory_id_customer_id
ON ONLY public.rental USING BTREE (rental_date pg_catalog.timestamp_ops ASC NULLS LAST, inventory_id pg_catalog.int4_ops ASC NULLS LAST, customer_id pg_catalog.int2_ops ASC NULLS LAST) ;
CREATE UNIQUE INDEX IF NOT EXISTS rental_pkey
ON ONLY public.rental USING BTREE (rental_id pg_catalog.int4_ops ASC NULLS LAST) ;
CREATE TABLE IF NOT EXISTS public.payment (
payment_id integer DEFAULT nextval('payment_payment_id_seq'::regclass) NOT NULL,
customer_id smallint NOT NULL,
staff_id smallint NOT NULL,
rental_id integer NOT NULL,
amount numeric(5,2) NOT NULL,
payment_date timestamp WITHOUT TIME ZONE NOT NULL,
CONSTRAINT payment_pkey PRIMARY KEY (payment_id),
CONSTRAINT payment_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES public.customer (customer_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT payment_rental_id_fkey FOREIGN KEY (rental_id) REFERENCES public.rental (rental_id) MATCH SIMPLE ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT payment_staff_id_fkey FOREIGN KEY (staff_id) REFERENCES public.staff (staff_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE
) TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS idx_fk_customer_id
ON ONLY public.payment USING BTREE (customer_id pg_catalog.int2_ops ASC NULLS LAST) ;
CREATE INDEX IF NOT EXISTS idx_fk_rental_id
ON ONLY public.payment USING BTREE (rental_id pg_catalog.int4_ops ASC NULLS LAST) ;
CREATE INDEX IF NOT EXISTS idx_fk_staff_id
ON ONLY public.payment USING BTREE (staff_id pg_catalog.int2_ops ASC NULLS LAST) ;
CREATE UNIQUE INDEX IF NOT EXISTS payment_pkey
ON ONLY public.payment USING BTREE (payment_id pg_catalog.int4_ops ASC NULLS LAST) ;
CREATE TABLE IF NOT EXISTS public.store (
store_id integer DEFAULT nextval('store_store_id_seq'::regclass) NOT NULL,
manager_staff_id smallint NOT NULL,
address_id smallint NOT NULL,
last_update timestamp WITHOUT TIME ZONE DEFAULT E'now()' NOT NULL,
CONSTRAINT store_pkey PRIMARY KEY (store_id),
CONSTRAINT store_address_id_fkey FOREIGN KEY (address_id) REFERENCES public.address (address_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT store_manager_staff_id_fkey FOREIGN KEY (manager_staff_id) REFERENCES public.staff (staff_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE
) TABLESPACE pg_default;
CREATE TRIGGER last_updated BEFORE UPDATE
ON public.store
FOR EACH ROW
EXECUTE FUNCTION last_updated();
CREATE UNIQUE INDEX IF NOT EXISTS idx_unq_manager_staff_id
ON ONLY public.store USING BTREE (manager_staff_id pg_catalog.int2_ops ASC NULLS LAST) ;
CREATE UNIQUE INDEX IF NOT EXISTS store_pkey
ON ONLY public.store USING BTREE (store_id pg_catalog.int4_ops ASC NULLS LAST) ;
CREATE VIEW public.actor_info
AS SELECT actor.actor_id,
actor.first_name,
actor.last_name,
FROM actor;
CREATE VIEW public.customer_list
AS SELECT customer.customer_id as id,
customer.first_name as name,
address.address,
address.postal_code as "zip code",
address.phone,
city.city,
country.country,
customer.activebool as notes,
customer.store_id as sid
FROM customer, address, city, country;
CREATE VIEW public.film_list
AS SELECT film.film_id as fid,
film.title,
film.description,
category.name as category,
film.rental_rate as price,
film.length,
film.rating,
actor.first_name as actors
FROM film, category, actor;
CREATE VIEW public.nicer_but_slower_film_list
AS SELECT film.film_id as fid,
film.title,
film.description,
category.name as category,
film.rental_rate as price,
film.length,
film.rating,
actor.first_name as actors
FROM film, category, actor;
CREATE VIEW public.sales_by_film_category
AS SELECT category.name as category,
payment.amount as total_sales
FROM category, payment;
CREATE VIEW public.sales_by_store
AS SELECT city.city as store,
actor.first_name as manager,
payment.amount as total_sales
FROM city, actor, payment;
CREATE VIEW public.staff_list
AS SELECT staff.staff_id as id,
staff.first_name as name,
address.address,
address.postal_code as "zip code",
address.phone,
city.city,
country.country,
staff.store_id as sid
FROM staff, address, city, country;| Property | Value |
| View name | actor_info |
| Technical name | |
| Activated | true |
| Id | |
| Schema | public |
| Additional properties | |
| Comments | |
| Or replace | false |
| Temporary | false |
| Recursive | false |
| View option | |
| [1] Check testing scope | |
| Security barrier | |
| Security invoker | |
| As query | |
| With check option | false |
| Remarks | |
| Column | Type | Req | Key | Description | Comments |
| actor_id | integer | false | |||
| first_name | varchar(45) | false | |||
| last_name | varchar(45) | false | |||
| film_info | reference | false |
| Property | Value |
| Business Name | actor_id |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | first_name |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | last_name |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | film_info |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
{
"$schema": "http://json-schema.org/draft-04/schema#",
"type": "object",
"title": "actor_info",
"properties": {
"actor_id": {
"type": "number",
"default": "nextval('actor_actor_id_seq'::regclass)"
},
"first_name": {
"type": "string"
},
"last_name": {
"type": "string"
},
"film_info": {}
},
"required": [
"actor_id",
"first_name",
"last_name"
]
}{
"actor_id": "nextval('actor_actor_id_seq'::regclass)",
"first_name": "Lorem",
"last_name": "Lorem",
"film_info": "Lorem"
}| Property | Value |
| View name | customer_list |
| Technical name | |
| Activated | true |
| Id | |
| Schema | public |
| Additional properties | |
| Comments | |
| Or replace | false |
| Temporary | false |
| Recursive | false |
| View option | |
| [1] Check testing scope | |
| Security barrier | |
| Security invoker | |
| As query | |
| With check option | false |
| Remarks | |
| Column | Type | Req | Key | Description | Comments |
| id | integer | false | |||
| name | varchar(45) | false | |||
| address | varchar(50) | false | |||
| zip code | varchar(10) | false | |||
| phone | varchar(20) | false | |||
| city | varchar(50) | false | |||
| country | varchar(50) | false | |||
| notes | boolean | false | |||
| sid | smallint | false |
| Property | Value |
| Business Name | id |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | name |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | address |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | zip code |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | phone |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | city |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | country |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | notes |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | sid |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
{
"$schema": "http://json-schema.org/draft-04/schema#",
"type": "object",
"title": "customer_list",
"properties": {
"id": {
"type": "number",
"default": "nextval('customer_customer_id_seq'::regclass)"
},
"name": {
"type": "string"
},
"address": {
"type": "string"
},
"zip code": {
"type": "string"
},
"phone": {
"type": "string"
},
"city": {
"type": "string"
},
"country": {
"type": "string"
},
"notes": {
"type": "boolean",
"default": true
},
"sid": {
"type": "number"
}
},
"required": [
"id",
"name",
"address",
"phone",
"city",
"country",
"notes",
"sid"
]
}{
"id": "nextval('customer_customer_id_seq'::regclass)",
"name": "Lorem",
"address": "Lorem",
"zip code": "Lorem",
"phone": "Lorem",
"city": "Lorem",
"country": "Lorem",
"notes": true,
"sid": -11
}| Property | Value |
| View name | film_list |
| Technical name | |
| Activated | true |
| Id | |
| Schema | public |
| Additional properties | |
| Comments | |
| Or replace | false |
| Temporary | false |
| Recursive | false |
| View option | |
| [1] Check testing scope | |
| Security barrier | |
| Security invoker | |
| As query | |
| With check option | false |
| Remarks | |
| Column | Type | Req | Key | Description | Comments |
| fid | integer | false | |||
| title | varchar(255) | false | |||
| description | text | false | |||
| category | varchar(25) | false | |||
| price | numeric(4, 2) | false | |||
| length | smallint | false | |||
| rating | enum | false | |||
| actors | varchar(45) | false |
| Property | Value |
| Business Name | fid |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | title |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | description |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | category |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | price |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | length |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | rating |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | actors |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
{
"$schema": "http://json-schema.org/draft-04/schema#",
"type": "object",
"title": "film_list",
"properties": {
"fid": {
"type": "number",
"default": "nextval('film_film_id_seq'::regclass)"
},
"title": {
"type": "string"
},
"description": {
"type": "string"
},
"category": {
"type": "string"
},
"price": {
"type": "number",
"default": "4.99"
},
"length": {
"type": "number"
},
"rating": {
"type": "string",
"enum": [
"G",
"PG",
"PG-13",
"R",
"NC-17"
]
},
"actors": {
"type": "string"
}
},
"required": [
"fid",
"title",
"category",
"price",
"actors"
]
}{
"fid": "nextval('film_film_id_seq'::regclass)",
"title": "Lorem",
"description": "Lorem",
"category": "Lorem",
"price": "4.99",
"length": 5,
"rating": "NC-17",
"actors": "Lorem"
}| Property | Value |
| View name | nicer_but_slower_film_list |
| Technical name | |
| Activated | true |
| Id | |
| Schema | public |
| Additional properties | |
| Comments | |
| Or replace | false |
| Temporary | false |
| Recursive | false |
| View option | |
| [1] Check testing scope | |
| Security barrier | |
| Security invoker | |
| As query | |
| With check option | false |
| Remarks | |
| Column | Type | Req | Key | Description | Comments |
| fid | integer | false | |||
| title | varchar(255) | false | |||
| description | text | false | |||
| category | varchar(25) | false | |||
| price | numeric(4, 2) | false | |||
| length | smallint | false | |||
| rating | enum | false | |||
| actors | varchar(45) | false |
| Property | Value |
| Business Name | fid |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | title |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | description |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | category |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | price |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | length |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | rating |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | actors |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
{
"$schema": "http://json-schema.org/draft-04/schema#",
"type": "object",
"title": "nicer_but_slower_film_list",
"properties": {
"fid": {
"type": "number",
"default": "nextval('film_film_id_seq'::regclass)"
},
"title": {
"type": "string"
},
"description": {
"type": "string"
},
"category": {
"type": "string"
},
"price": {
"type": "number",
"default": "4.99"
},
"length": {
"type": "number"
},
"rating": {
"type": "string",
"enum": [
"G",
"PG",
"PG-13",
"R",
"NC-17"
]
},
"actors": {
"type": "string"
}
},
"required": [
"fid",
"title",
"category",
"price",
"actors"
]
}{
"fid": "nextval('film_film_id_seq'::regclass)",
"title": "Lorem",
"description": "Lorem",
"category": "Lorem",
"price": "4.99",
"length": -82,
"rating": "NC-17",
"actors": "Lorem"
}| Property | Value |
| View name | sales_by_film_category |
| Technical name | |
| Activated | true |
| Id | |
| Schema | public |
| Additional properties | |
| Comments | |
| Or replace | false |
| Temporary | false |
| Recursive | false |
| View option | |
| [1] Check testing scope | |
| Security barrier | |
| Security invoker | |
| As query | |
| With check option | false |
| Remarks | |
| Column | Type | Req | Key | Description | Comments |
| category | varchar(25) | false | |||
| total_sales | numeric(5, 2) | false |
| Property | Value |
| Business Name | category |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | total_sales |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
{
"$schema": "http://json-schema.org/draft-04/schema#",
"type": "object",
"title": "sales_by_film_category",
"properties": {
"category": {
"type": "string"
},
"total_sales": {
"type": "number"
}
},
"required": [
"category",
"total_sales"
]
}{
"category": "Lorem",
"total_sales": 56
}| Property | Value |
| View name | sales_by_store |
| Technical name | |
| Activated | true |
| Id | |
| Schema | public |
| Additional properties | |
| Comments | |
| Or replace | false |
| Temporary | false |
| Recursive | false |
| View option | |
| [1] Check testing scope | |
| Security barrier | |
| Security invoker | |
| As query | |
| With check option | false |
| Remarks | |
| Column | Type | Req | Key | Description | Comments |
| store | varchar(50) | false | |||
| manager | varchar(45) | false | |||
| total_sales | numeric(5, 2) | false |
| Property | Value |
| Business Name | store |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | manager |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | total_sales |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
{
"$schema": "http://json-schema.org/draft-04/schema#",
"type": "object",
"title": "sales_by_store",
"properties": {
"store": {
"type": "string"
},
"manager": {
"type": "string"
},
"total_sales": {
"type": "number"
}
},
"required": [
"store",
"manager",
"total_sales"
]
}{
"store": "Lorem",
"manager": "Lorem",
"total_sales": -64
}| Property | Value |
| View name | staff_list |
| Technical name | |
| Activated | true |
| Id | |
| Schema | public |
| Additional properties | |
| Comments | |
| Or replace | false |
| Temporary | false |
| Recursive | false |
| View option | |
| [1] Check testing scope | |
| Security barrier | |
| Security invoker | |
| As query | |
| With check option | false |
| Remarks | |
| Column | Type | Req | Key | Description | Comments |
| id | integer | false | |||
| name | varchar(45) | false | |||
| address | varchar(50) | false | |||
| zip code | varchar(10) | false | |||
| phone | varchar(20) | false | |||
| city | varchar(50) | false | |||
| country | varchar(50) | false | |||
| sid | smallint | false |
| Property | Value |
| Business Name | id |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | name |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | address |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | zip code |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | phone |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | city |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | country |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
| Property | Value |
| Business Name | sid |
| Technical name | |
| Activated | true |
| Reference type | collectionReference |
| Reference description | |
| Lineage capture | |
| Description | |
| Transformation | |
| Sources | |
{
"$schema": "http://json-schema.org/draft-04/schema#",
"type": "object",
"title": "staff_list",
"properties": {
"id": {
"type": "number",
"default": "nextval('staff_staff_id_seq'::regclass)"
},
"name": {
"type": "string"
},
"address": {
"type": "string"
},
"zip code": {
"type": "string"
},
"phone": {
"type": "string"
},
"city": {
"type": "string"
},
"country": {
"type": "string"
},
"sid": {
"type": "number"
}
},
"required": [
"id",
"name",
"address",
"phone",
"city",
"country",
"sid"
]
}{
"id": "nextval('staff_staff_id_seq'::regclass)",
"name": "Lorem",
"address": "Lorem",
"zip code": "Lorem",
"phone": "Lorem",
"city": "Lorem",
"country": "Lorem",
"sid": 3
}| Property | Value |
| Name | customer_address_id_fkey |
| Description | |
| Parent Table | address |
| Parent Column | address_id |
| Parent Cardinality | 1 |
| Child Table | customer |
| Child Column | address_id |
| Child Cardinality | 0..n |
| Comments | |
| relationshipOnDelete | RESTRICT |
| Property | Value |
| Name | film_actor_actor_id_fkey |
| Description | |
| Parent Table | actor |
| Parent Column | actor_id |
| Parent Cardinality | 1 |
| Child Table | film_actor |
| Child Column | actor_id |
| Child Cardinality | 0..n |
| Comments | |
| relationshipOnDelete | RESTRICT |
| Property | Value |
| Name | film_actor_film_id_fkey |
| Description | |
| Parent Table | film |
| Parent Column | film_id |
| Parent Cardinality | 1 |
| Child Table | film_actor |
| Child Column | film_id |
| Child Cardinality | 0..n |
| Comments | |
| relationshipOnDelete | RESTRICT |
| Property | Value |
| Name | film_category_category_id_fkey |
| Description | |
| Parent Table | category |
| Parent Column | category_id |
| Parent Cardinality | 1 |
| Child Table | film_category |
| Child Column | category_id |
| Child Cardinality | 0..n |
| Comments | |
| relationshipOnDelete | RESTRICT |
| Property | Value |
| Name | film_category_film_id_fkey |
| Description | |
| Parent Table | film |
| Parent Column | film_id |
| Parent Cardinality | 1 |
| Child Table | film_category |
| Child Column | film_id |
| Child Cardinality | 0..n |
| Comments | |
| relationshipOnDelete | RESTRICT |
| Property | Value |
| Name | film_language_id_fkey |
| Description | |
| Parent Table | language |
| Parent Column | language_id |
| Parent Cardinality | 1 |
| Child Table | film |
| Child Column | language_id |
| Child Cardinality | 0..n |
| Comments | |
| relationshipOnDelete | RESTRICT |
| Property | Value |
| Name | fk_city |
| Description | |
| Parent Table | country |
| Parent Column | country_id |
| Parent Cardinality | 1 |
| Child Table | city |
| Child Column | country_id |
| Child Cardinality | 0..n |
| Comments | |
| relationshipOnDelete | NO ACTION |
| Property | Value |
| Name | payment_customer_id_fkey |
| Description | |
| Parent Table | customer |
| Parent Column | customer_id |
| Parent Cardinality | 1 |
| Child Table | payment |
| Child Column | customer_id |
| Child Cardinality | 0..n |
| Comments | |
| relationshipOnDelete | RESTRICT |
| Property | Value |
| Name | rental_customer_id_fkey |
| Description | |
| Parent Table | customer |
| Parent Column | customer_id |
| Parent Cardinality | 1 |
| Child Table | rental |
| Child Column | customer_id |
| Child Cardinality | 0..n |
| Comments | |
| relationshipOnDelete | RESTRICT |
| Property | Value |
| Name | rental_inventory_id_fkey |
| Description | |
| Parent Table | inventory |
| Parent Column | inventory_id |
| Parent Cardinality | 1 |
| Child Table | rental |
| Child Column | inventory_id |
| Child Cardinality | 0..n |
| Comments | |
| relationshipOnDelete | RESTRICT |
| Property | Value |
| Name | staff_address_id_fkey |
| Description | |
| Parent Table | address |
| Parent Column | address_id |
| Parent Cardinality | 1 |
| Child Table | staff |
| Child Column | address_id |
| Child Cardinality | 0..n |
| Comments | |
| relationshipOnDelete | RESTRICT |
| Property | Value |
| Name | store_address_id_fkey |
| Description | |
| Parent Table | address |
| Parent Column | address_id |
| Parent Cardinality | 1 |
| Child Table | store |
| Child Column | address_id |
| Child Cardinality | 0..n |
| Comments | |
| relationshipOnDelete | RESTRICT |
| Property | Value |
| Name | store_manager_staff_id_fkey |
| Description | |
| Parent Table | staff |
| Parent Column | staff_id |
| Parent Cardinality | 1 |
| Child Table | store |
| Child Column | manager_staff_id |
| Child Cardinality | 0..n |
| Comments | |
| relationshipOnDelete | RESTRICT |